from rest_framework.views import APIView
from rest_framework.response import Response
from django_redis import get_redis_connection
from random import randint
from rest_framework import status
import logging, datetime, jwt, pymysql, hashlib, requests, time, json, re, xlrd, math, rsa
from django.utils import timezone
from django.conf import settings
from rest_framework.viewsets import ModelViewSet
from rest_framework import filters
from django_filters.rest_framework import DjangoFilterBackend
from django.shortcuts import render
from django.http import StreamingHttpResponse
from django.utils.encoding import escape_uri_path
from django.db.models import Q, F, Func, Value, CharField
from django.db.models.functions import Concat
from django.db import transaction
from elasticsearch import Elasticsearch
from django.db import connection
from django.db.models.expressions import RawSQL
from geopy.distance import geodesic
from PIL import Image
import io

from equity_admin.serializers import *
from equity_mall.utils.common_msg import common_msg
from equity_admin.filters import *
from equity_mall.utils.utils import get_level_list, random_str, get_sign, ryx_profitsharing_order, get_sign_HMACSHA1
from equity_mall.utils.qiniu_storage import pic_upload, pic_delete
from equity_mall.utils.sync_img_to_local import sync_img_to_local, make_qrcode, make_qrcode2
from equity_admin.tasks import *
from equity_admin.utils import get_stockinfo, get_alidiscountcoupon
from customers.utils import AlipayOpen as C_AlipayOpen
from equity_mall.utils.gzh_send_templates import ali_fundtrans_template
from equity_mall.celery import celery_app
from equity_mall.utils.pages import MyPage
from equity_mall.utils.express_utils import KuaiDi100
from equity_admin.serializers1 import *
from equity_mall.utils.wash_level1 import ylx_api

logger = logging.getLogger('django')


class WaivePaymentActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentActivityFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('activity_name', )
    queryset = WaivePaymentActivity.objects.all().order_by('-id')
    serializer_class = WaivePaymentActivityModelSerializer

    def create(self, request, *args, **kwargs):
        user = self.request.iser
        activity_name = self.request.data.get('activity_name', None)
        available_begin_time = self.request.data.get('available_begin_time', None)
        available_end_time = self.request.data.get('available_end_time', None)
        desc = self.request.data.get('desc', None)
        description = self.request.data.get('description', None)
        is_repeat = self.request.data.get('is_repeat', None)
        if not all([activity_name, available_begin_time, available_end_time, desc, description]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            datetime.datetime.strptime(available_begin_time, '%Y-%m-%d %H:%M:%S')
            datetime.datetime.strptime(available_end_time, '%Y-%m-%d %H:%M:%S')
        except:
            return Response({'error': '活动时间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if str(is_repeat) not in ['0', '1']:
            return Response({'error': '无效重复校验参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = WaivePaymentActivity.objects.create(
                activity_name=activity_name,
                available_end_time=available_end_time,
                available_begin_time=available_begin_time,
                desc=desc,
                description=description,
                is_repeat=is_repeat
            )
        except:
            return Response({'error': '创建失败'}, status=status.HTTP_400_BAD_REQUEST)
        WaivePaymentAdvance.objects.create(
            waivepaymentactivity_id=instance.id,
            activity_state=1,
            remark='创建活动',
            create_user_id=user.id
        )
        return Response({'success': '创建成功', 'id': instance.id}, status=status.HTTP_200_OK)

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        my_file = self.request.FILES.get('my_file', None)
        trade_time_begin = self.request.data.get('trade_time_begin', None)
        trade_time_end = self.request.data.get('trade_time_end', None)
        name = self.request.data.get('name', None)
        if not all([trade_time_begin, trade_time_end]):
            return Response({'error': '请上传交易时间区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            trade_time_begin_stamp = int(time.mktime(time.strptime(trade_time_begin, '%Y-%m-%d %H:%M:%S')))
            trade_time_end_stamp = int(time.mktime(time.strptime(trade_time_end, '%Y-%m-%d %H:%M:%S')))
        except:
            return Response({'error': '交易时间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if (trade_time_end_stamp - trade_time_begin_stamp) > 1209600:
            return Response({'error': '交易时间区间不得大于14天'}, status=status.HTTP_400_BAD_REQUEST)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        title = table.row_values(0)
        nrows = table.nrows - 1
        if '商户号' not in title:
            return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
        if '是否中奖' not in title:
            return Response({'error': '表格字段缺少"是否中奖"'}, status=status.HTTP_400_BAD_REQUEST)
        if '期数' not in title:
            return Response({'error': '表格字段缺少"期数"'}, status=status.HTTP_400_BAD_REQUEST)
        if str(table.row_values(1)[title.index('期数')]) not in ['一', '二', '三', '四', '五', '六']:
            return Response({'error': '期数参数有误'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        waivepaymentadvance = WaivePaymentAdvance.objects.create(
            waivepaymentactivity_id=instance.id,
            activity_state=instance.activity_state,
            remark='上传活动商户',
            create_user_id=user.id,
            file=file,
            trade_time_begin=trade_time_begin,
            trade_time_end=trade_time_end,
            operate_type=2,
            name=name,
            join_number=nrows
        )
        instance.join_number += nrows
        instance.save()
        pull_waive_payment_merchant.delay({'id': waivepaymentadvance.id})
        # update_stock_usable_merchant({'id': history.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)

    def partial_update(self, request, *args, **kwargs):
        instance = self.get_object()
        user = self.request.iser
        if instance.activity_state == 4:
            return Response({'error': '已结束活动无法操作'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_state == 2:
            instance.activity_state = 3
            instance.save()
            WaivePaymentAdvance.objects.create(
                waivepaymentactivity_id=instance.id,
                activity_state=3,
                remark='暂停活动',
                create_user_id=user.id
            )
        elif instance.activity_state in [1, 3]:
            instance.activity_state = 2
            instance.save()
            WaivePaymentAdvance.objects.create(
                waivepaymentactivity_id=instance.id,
                activity_state=2,
                remark='开始活动',
                create_user_id=user.id
            )
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class WaivePaymentAdvanceModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentAdvanceFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name', )
    queryset = WaivePaymentAdvance.objects.all().order_by('-id')
    serializer_class = WaivePaymentAdvanceModelSerializer

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        if instance.payment_state == 1:
            return Response({'error': '子活动状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.operate_type == 1:
            return Response({'error': '子活动类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        instance.activate_user_id = user.id
        instance.activate_time = datetime.datetime.now()
        instance.payment_state = 1
        instance.save()
        instance.waivepaymentmerchant_set.filter(payment_state=0).update(payment_state=5)
        # WaivePaymentAdvance.objects.create(
        #     waivepaymentactivity_id=instance.waivepaymentactivity_id,
        #     activity_state=2,
        #     remark=f'激活商户,文件:{instance.id},{instance.name}',
        #     create_user_id=user.id
        # )
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class WaivePaymentMerchantModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentMerchantFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('waivepaymentactivity__activity_name', 'business_no')
    queryset = WaivePaymentMerchant.objects.all().order_by('-id')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = WaivePaymentMerchant.objects.all()
        elif level.level == 1:
            queryset = WaivePaymentMerchant.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = WaivePaymentMerchant.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = WaivePaymentMerchant.objects.filter(level__district_id=level.id)
        else:
            queryset = WaivePaymentMerchant.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return WaivePaymentMerchantListModelSerializer
        else:
            return WaivePaymentMerchantRetrieveModelSerializer

    def export_waivepaymentmerchant(self, request):
        user = self.request.iser
        waivepaymentactivity_id = self.request.query_params.get('waivepaymentactivity_id', None)
        waivepaymentadvance_id = self.request.query_params.get('waivepaymentadvance_id', None)
        level_id = self.request.query_params.get('level', None)
        payment_state = self.request.query_params.get('payment_state', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE level_id IN (SELECT id FROM tb_u_level)'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        if payment_state:
            condition = condition + f' AND payment_state in ({payment_state})'
        if waivepaymentadvance_id:
            condition = condition + f' AND waivepaymentadvance_id={waivepaymentadvance_id}'
        if waivepaymentactivity_id:
            condition = condition + f' AND waivepaymentactivity_id={waivepaymentactivity_id}'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT i.*,j.mobile FROM (SELECT g.*,h.city_name,district_name,branch_name FROM (SELECT e.*,f.waivepaymentmerchant_id,trade_money,trade_type,trade_time,trade_no FROM (SELECT c.*,d.merchant_name,short_name FROM (SELECT a.*,b.activity_name FROM (SELECT id,waivepaymentactivity_id,business_no,merchant_id,merchant_user_id,account,name,payment_no,amount,payment_state,DATE_FORMAT(payment_time,'%Y-%m-%d %H:%i:%S')AS payment_time,company_mobile,return_msg,level_id,periods FROM tb_waive_payment_merchant {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,name AS merchant_name,short_name FROM tb_merchant)AS d ON c.merchant_id=d.id)AS e LEFT JOIN (SELECT id,waivepaymentmerchant_id,trade_money,trade_type,DATE_FORMAT(trade_time_end,'%Y-%m-%d %H:%i:%S')AS trade_time,trade_no FROM tb_waive_payment_order WHERE is_hit=1)AS f ON e.id=f.waivepaymentmerchant_id)AS g LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS j ON i.merchant_user_id=j.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT i.*,j.mobile FROM (SELECT g.*,h.city_name,district_name,branch_name FROM (SELECT e.*,f.waivepaymentmerchant_id,trade_money,trade_type,trade_time,trade_no FROM (SELECT c.*,d.merchant_name,short_name FROM (SELECT a.*,b.activity_name FROM (SELECT id,waivepaymentactivity_id,business_no,merchant_id,merchant_user_id,account,name,payment_no,amount,payment_state,DATE_FORMAT(payment_time,'%Y-%m-%d %H:%i:%S')AS payment_time,company_mobile,return_msg,level_id,periods FROM tb_waive_payment_merchant)AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,name AS merchant_name,short_name FROM tb_merchant)AS d ON c.merchant_id=d.id)AS e LEFT JOIN (SELECT id,waivepaymentmerchant_id,trade_money,trade_type,DATE_FORMAT(trade_time_end,'%Y-%m-%d %H:%i:%S')AS trade_time,trade_no FROM tb_waive_payment_order WHERE is_hit=1)AS f ON e.id=f.waivepaymentmerchant_id)AS g LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,mobile FROM tb_m_merchant_user)AS j ON i.merchant_user_id=j.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '商户号', '商户全称', '商户简称', '归属市公司', '归属区县', '归属网点',
                  '领取手机号', '领取支付宝账户', '支付宝实名信息', '打款支付宝订单号', '免单订单号', '交易金额', '交易类型', '交易时间', '期数', '是否中奖', '免单金额',
                  '领取状态', '领取时间', '参与码', '领取失败原因']
        cols = ['id', 'activity_name', 'business_no', 'merchant_name', 'short_name', 'city_name', 'district_name', 'branch_name',
                'mobile', 'account', 'name', 'payment_no', 'trade_no', 'trade_money', 'trade_type', 'trade_time', 'periods', 'is_draw', 'amount', 'payment_state',
                'payment_time', 'company_mobile', 'return_msg']
        response = StreamingHttpResponse(self.get_waivepaymentmerchant_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('月月免单领取明细.csv'))
        return response

    def get_waivepaymentmerchant_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        TRADE_TYPE_ENUM = {
            1: '支付宝',
            2: '微信',
            3: '云闪付',
            4: '邮储手机银行通道'
        }
        PAYMENT_STATE_ENUM = {
            0: '未激活',
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            4: '已撤销',
            5: '待抽奖',
            6: '无效',
            7: '谢谢参与'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'trade_type':
                    try:
                        tmp_str += '"%s",' % (TRADE_TYPE_ENUM[row[col]])
                    except:
                        tmp_str += '"%s",' % ('')
                elif col == 'is_draw':
                    try:
                        if row['amount']:
                            tmp_str += '"%s",' % ('是')
                        else:
                            tmp_str += '"%s",' % ('否')
                    except:
                        tmp_str += '"%s",' % ('')
                elif col == 'payment_state':
                    tmp_str += '"%s",' % (PAYMENT_STATE_ENUM[row[col]])
                # elif col == 'amount':
                #     if row['payment_state'] in [1, 2, 3]:
                #         tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                #     else:
                #         tmp_str += '"%s",' % ''
                elif col in ['payment_no', 'trade_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class WaivePaymentOrderModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = WaivePaymentOrderFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('waivepaymentactivity__activity_name', 'business_no')
    queryset = WaivePaymentOrder.objects.all().order_by('-is_hit')
    serializer_class = WaivePaymentOrderModelSerializer


    def export_waivepaymentorder(self, request):
        user = self.request.iser
        waivepaymentactivity_id = self.request.query_params.get('waivepaymentactivity_id', None)
        waivepaymentmerchant_id = self.request.query_params.get('waivepaymentmerchant_id', None)
        # level_id = self.request.query_params.get('level', None)
        # payment_state = self.request.query_params.get('payment_state', None)
        # search = self.request.query_params.get('search', None)
        # level = user.level
        # if level_id:
        #     try:
        #         level_list = get_level_list(level)
        #         level_id = int(level_id)
        #         if level_id in level_list:
        #             level = Level.objects.filter(id=level_id).first()
        #     except:
        #         pass
        # if level.level == 0:
        #     condition = 'WHERE level_id IN (SELECT id FROM tb_u_level) AND payment_state!=6'
        # elif level.level == 1:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id}) AND payment_state!=6'
        # elif level.level == 2:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id}) AND payment_state!=6'
        # elif level.level == 3:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id}) AND payment_state!=6'
        # else:
        #     condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id}) AND payment_state!=6'
        # if search:
        #     condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        # if payment_state:
        #     condition = condition + f' AND payment_state in ({payment_state})'
        if waivepaymentmerchant_id and waivepaymentactivity_id:
            condition = f'WHERE waivepaymentmerchant_id={waivepaymentmerchant_id} AND waivepaymentactivity_id={waivepaymentactivity_id}'
        elif waivepaymentactivity_id:
            condition = f'WHERE waivepaymentactivity_id={waivepaymentactivity_id}'
        elif waivepaymentmerchant_id:
            condition = f'WHERE waivepaymentmerchant_id={waivepaymentmerchant_id}'
        else:
            condition = ''
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT c.*,d.business_no FROM (SELECT a.*,b.activity_name FROM (SELECT id,order_no,trade_no,trade_type,trade_money,DATE_FORMAT(trade_time_begin,'%Y-%m-%d %H:%i:%S')AS payment_time,channel,waivepaymentactivity_id,waivepaymentmerchant_id,is_hit FROM tb_waive_payment_order {condition})AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,business_no FROM tb_waive_payment_merchant)AS d ON c.waivepaymentmerchant_id=d.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT c.*,d.business_no FROM (SELECT a.*,b.activity_name FROM (SELECT id,order_no,trade_no,trade_type,trade_money,DATE_FORMAT(trade_time_begin,'%Y-%m-%d %H:%i:%S')AS payment_time,channel,waivepaymentactivity_id,waivepaymentmerchant_id,is_hit FROM tb_waive_payment_order)AS a LEFT JOIN (SELECT id,activity_name FROM tb_waive_payment_activity)AS b ON a.waivepaymentactivity_id=b.id)AS c LEFT JOIN (SELECT id,business_no FROM tb_waive_payment_merchant)AS d ON c.waivepaymentmerchant_id=d.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '活动名称', '商户号', '订单号', '流水号', '交易类型', '交易金额', '交易时间', '交易通道', '是否命中']
        cols = ['id', 'activity_name', 'business_no', 'order_no', 'trade_no', 'trade_type', 'trade_money', 'payment_time', 'channel', 'is_hit']
        response = StreamingHttpResponse(self.get_waivepaymentorder_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('月月免单领取明细.csv'))
        return response

    def get_waivepaymentorder_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        TRADE_TYPE_ENUM = {
            1: '支付宝',
            2: '微信',
            3: '云闪付'
        }
        IS_HIT_ENUM = {
            0: '未命中',
            1: '已命中'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'trade_type':
                    tmp_str += '"%s",' % (TRADE_TYPE_ENUM[row[col]])
                elif col == 'is_hit':
                    tmp_str += '"%s",' % (IS_HIT_ENUM[row[col]])
                # elif col == 'amount':
                #     if row['payment_state'] in [1, 2, 3]:
                #         tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                #     else:
                #         tmp_str += '"%s",' % ''
                elif col in ['order_no', 'trade_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class BlackTableModelViewSet(ModelViewSet):

    pagination_class = MyPage
    queryset = BlackTable.objects.all()
    serializer_class = BlackTableModelSerializer
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('stock_id', )

    def create(self, request, *args, **kwargs):
        stock_array = self.request.data.get('stock_array', None)
        if not stock_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()

            # 暴力回滚
            try:
                for stock_id in stock_array:
                    BlackTable.objects.get_or_create(defaults={"stock_id": stock_id}, stock_id=stock_id)
                transaction.savepoint_commit(save_id)
                return Response({'success': '添加成功'}, status=status.HTTP_200_OK)
            except Exception as e:
                logger.error(e)
                # 事务回滚
                transaction.savepoint_rollback(save_id)
                return Response({'error': '添加失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        instance.delete()
        return Response({'success': '删除成功'}, status=status.HTTP_200_OK)


class MerchantSettlementRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantSettlementRecordFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__name', 'merchant__short_name', 'merchant__wyfMerchantID')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantSettlementRecord.objects.all()
        elif level.level == 1:
            queryset = MerchantSettlementRecord.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantSettlementRecord.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantSettlementRecord.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantSettlementRecord.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-settlement_time', 'id')

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantSettlementRecordListModelSerializer
        else:
            return MerchantSettlementRecordRetrieveModelSerializer

    def export_settlementrecord(self, request):
        user = self.request.iser
        settlement_time_begin = self.request.query_params.get('settlement_time_begin', None)
        settlement_time_end = self.request.query_params.get('settlement_time_end', None)
        level_id = self.request.query_params.get('level', None)
        status = self.request.query_params.get('status', None)
        channel_id = self.request.query_params.get('channel_id', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE level_id IN (SELECT id FROM tb_u_level)'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (merchant_id IN (SELECT id FROM tb_merchant WHERE name LIKE "%{search}%" OR short_name LIKE "%{search}%" OR wyfMerchantID LIKE "%{search}%"))'
        if status:
            condition = condition + f' AND status in ({status})'
        if channel_id:
            condition = condition + f' AND channel_id in ({channel_id})'
        if settlement_time_begin:
            condition = condition + f' AND settlement_time >= "{settlement_time_begin + " 00:00:00"}"'
        if settlement_time_end:
            condition = condition + f' AND settlement_time <= "{settlement_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT * FROM (SELECT e.*,f.channel_name FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.merchant_name,short_name,wyfMerchantID FROM (SELECT id,account_name,account_number,status,amount,DATE_FORMAT(settlement_time,'%Y-%m-%d %H:%i:%S')AS settlement_time,fail_reason,merchant_id,channel_id,level_id FROM tb_merchant_settlement_record {condition})AS a LEFT JOIN (SELECT id,name AS merchant_name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS f ON e.channel_id=f.id)AS j LEFT JOIN (SELECT settlement_record_id,GROUP_CONCAT(payable_amount)AS payable_amount,GROUP_CONCAT(DATE_FORMAT(receipts_time,'%Y-%m-%d'))AS receipts_time FROM (SELECT g.bill_id,settlement_record_id,h.payable_amount,receipts_time FROM (SELECT bill_id,settlement_record_id FROM tb_merchant_bill_settlement_record)AS g LEFT JOIN (SELECT id,receipts_time,payable_amount FROM tb_merchant_bill)AS h ON g.bill_id=h.id)AS i GROUP BY settlement_record_id)AS k ON j.id=k.settlement_record_id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT * FROM (SELECT e.*,f.channel_name FROM (SELECT c.*,d.city_name,district_name,branch_name FROM (SELECT a.*,b.merchant_name,short_name,wyfMerchantID FROM (SELECT id,account_name,account_number,status,amount,DATE_FORMAT(settlement_time,'%Y-%m-%d %H:%i:%S')AS settlement_time,fail_reason,merchant_id,channel_id,level_id FROM tb_merchant_settlement_record)AS a LEFT JOIN (SELECT id,name AS merchant_name,short_name,wyfMerchantID FROM tb_merchant)AS b ON a.merchant_id=b.id)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,name AS channel_name FROM tb_channel)AS f ON e.channel_id=f.id)AS j LEFT JOIN (SELECT settlement_record_id,GROUP_CONCAT(payable_amount)AS payable_amount,GROUP_CONCAT(DATE_FORMAT(receipts_time,'%Y-%m-%d'))AS receipts_time FROM (SELECT g.bill_id,settlement_record_id,h.payable_amount,receipts_time FROM (SELECT bill_id,settlement_record_id FROM tb_merchant_bill_settlement_record)AS g LEFT JOIN (SELECT id,receipts_time,payable_amount FROM tb_merchant_bill)AS h ON g.bill_id=h.id)AS i GROUP BY settlement_record_id)AS k ON j.id=k.settlement_record_id;
            '''
            cursor.execute(sql)
        ziduan = ['结算ID', '商户全称', '商户简称', '微邮付商户号', '清分日期', '结算时间', '清分金额', '结算金额',
                  '结算状态', '结算失败原因', '归属市公司', '归属区县', '归属网点', '结算通道', '结算户名', '结算卡号']
        cols = ['id', 'merchant_name', 'short_name', 'wyfMerchantID', 'receipts_time', 'settlement_time', 'payable_amount', 'amount',
                'status', 'fail_reason', 'city_name', 'district_name', 'branch_name', 'channel_name', 'account_name', 'account_number']
        response = StreamingHttpResponse(self.get_settlementrecord_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算单信息.csv'))
        return response

    def get_settlementrecord_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATUS_ENUM = {
            1: '待结算',
            2: '结算中',
            3: '已结算',
            4: '已撤销',
            5: '结算失败'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'status':
                    tmp_str += '"%s",' % (STATUS_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def synchronization_settlementrecord(self, request):
        user = self.request.iser
        start = self.request.data.get('start', None)
        end = self.request.data.get('end', None)
        merchant_id = self.request.data.get('merchant_id', None)
        if not all([start, end]):
            return Response({'error': '请上传交易区间'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            start1 = datetime.datetime.strptime(start, '%Y-%m-%d')
            end1 = datetime.datetime.strptime(end, '%Y-%m-%d')
        except:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if start1 > end1:
            return Response({'error': '交易区间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchant = Merchant.objects.get(id=merchant_id)
        except:
            return Response({'error': '错误商户号'}, status=status.HTTP_400_BAD_REQUEST)
        synchronization_settlementrecord.delay({'merchant_id': merchant_id, 'start': start, 'end': end, 'user_id': user.id})
        # synchronization_settlementrecord({'merchant_id': merchant_id, 'start': start, 'end': end})
        return Response({'success': '系统同步交易中'}, status=status.HTTP_200_OK)

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.is_bind == 1:
            return Response({'error': '结算流水已绑定,不可重复操作'}, status=status.HTTP_400_BAD_REQUEST)
        bill_array = self.request.data.get('bill_array', None)
        if not bill_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            merchantbill_set = MerchantBill.objects.filter(bill_state__in=[1, 5], merchant_id=instance.merchant_id, id__in=bill_array, receipts_time__lte=instance.settlement_time)
        except:
            return Response({'error': '无效清分记录参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not merchantbill_set:
            return Response({'error': '无效清分记录参数'}, status=status.HTTP_400_BAD_REQUEST)
        total_amount = merchantbill_set.aggregate(Sum('payable_amount'))['payable_amount__sum']
        if instance.amount != total_amount:
            return Response({'error': '清分金额与结算金额不符'}, status=status.HTTP_400_BAD_REQUEST)
        for merchantbill in merchantbill_set:
            relations_data = {}
            relations_data['settlement_record_id'] = instance.id
            relations_data['bill_id'] = merchantbill.id
            MerchantBillSettlementRecord.objects.get_or_create(defaults=relations_data, settlement_record_id=instance.id, bill_id=merchantbill.id)
        merchantbill_set.update(
            account_name=instance.account_name,
            account_bank=instance.account_bank,
            account_number=instance.account_number,
            settlement_time=instance.settlement_time,
            settlement_record_id=instance.id,
            bill_state=instance.status,
            settlement_amount=instance.amount,
            channel_id=instance.channel_id
        )
        instance.is_bind = 1
        instance.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def partial_update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.is_bind == 0:
            return Response({'error': '结算流水未绑定,无须解绑'}, status=status.HTTP_400_BAD_REQUEST)
        MerchantBill.objects.filter(merchantbillsettlementrecord__settlement_record_id=instance.id).update(
            bill_state=1,
            settlement_record_id=None
        )
        merchantbillsettlementrecord_set = MerchantBillSettlementRecord.objects.filter(settlement_record_id=instance.id).delete()
        instance.is_bind = 0
        instance.save()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.is_bind == 1:
            return Response({'error': '已绑定的结算流水,不可删除'}, status=status.HTTP_400_BAD_REQUEST)

    def statistical_settlement(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        if time_max1 - time_min1 > 2678400:
            return Response({'error': '时区不得大于31天!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        sxf_amount_data = {}
        ryx_amount_data = {}
        total_amount_data = {}
        sxf_data = []
        ryx_data = []
        total_data = []
        data = {}
        max_amount = 0
        sxf_amount_sum = 0
        ryx_amount_sum = 0
        total_amount_sum = 0
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        xAxis = []
        time_min = datetime.datetime.strptime(time_min, "%Y-%m-%d").date()
        time_max = datetime.datetime.strptime(time_max, "%Y-%m-%d").date()
        create_begin_time = time_min
        days_num = (time_max-time_min).days + 1
        begin_day = time_min
        sql = f'''SELECT DATE_FORMAT(settlement_time,'%Y-%m-%d')AS time,SUM(CASE channel_id WHEN 3 THEN amount ELSE 0 END)AS sxf_amount,SUM(CASE channel_id WHEN 2 THEN amount ELSE 0 END)AS ryx_amount,SUM(amount)AS total_amount FROM tb_merchant_settlement_record WHERE settlement_time>="{time_min1}" AND settlement_time<="{time_max1}" AND status=3 {condition} GROUP BY time;'''
        cursor.execute(sql)
        result = cursor.fetchall()
        while True:
            if begin_day > time_max:
                break
            xAxis.append(datetime.datetime.strftime(begin_day, '%m/%d'))
            begin_day += datetime.timedelta(days=1)
        if result:
            while True:
                if datetime.datetime.strptime(result[0]['time'], "%Y-%m-%d").date() <= create_begin_time:
                    break
                sxf_data.append(0)
                ryx_data.append(0)
                total_data.append(0)
                create_begin_time += datetime.timedelta(days=1)
            for x in result:
                while True:
                    if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() < create_begin_time:
                        break
                    if datetime.datetime.strptime(x['time'], "%Y-%m-%d").date() == create_begin_time:
                        sxf_data.append(x['sxf_amount'])
                        ryx_data.append(x['ryx_amount'])
                        total_data.append(x['total_amount'])
                        sxf_amount_sum += x['sxf_amount']
                        ryx_amount_sum += x['ryx_amount']
                        total_amount_sum += x['total_amount']
                        if max_amount < x['total_amount']:
                            max_amount = x['total_amount']
                    else:
                        sxf_data.append(0)
                        ryx_data.append(0)
                        total_data.append(0)
                    create_begin_time += datetime.timedelta(days=1)
            while True:
                if len(sxf_data) >= days_num:
                    break
                sxf_data.append(0)
                ryx_data.append(0)
                total_data.append(0)
        else:
            for i in range(days_num):
                sxf_data.append(0)
                ryx_data.append(0)
                total_data.append(0)
        sxf_amount_data['name'] = '随行付结算金额'
        sxf_amount_data['data'] = sxf_data
        ryx_amount_data['name'] = '瑞银信结算金额'
        ryx_amount_data['data'] = ryx_data
        total_amount_data['name'] = '总结算金额'
        total_amount_data['data'] = total_data
        cursor.close()
        conn.close()
        if max_amount:
            str_max_amount = str(max_amount)
            if '.' in str_max_amount:
                len_max_amount = len(str_max_amount[:str_max_amount.find('.')])
            else:
                len_max_amount = len(str_max_amount)
            to_max_amount = '1'
            while True:
                if len(to_max_amount) >= len_max_amount -1:
                    to_max_amount = int(to_max_amount)
                    break
                to_max_amount += '0'
            max_amount = math.ceil(max_amount/to_max_amount)*to_max_amount
        data['sxf_amount_data'] = sxf_amount_data
        data['ryx_amount_data'] = ryx_amount_data
        data['total_amount_data'] = total_amount_data
        data['max_amount'] = max_amount
        data['xAxis'] = xAxis
        data['ryx_amount_sum'] = ryx_amount_sum
        data['sxf_amount_sum'] = sxf_amount_sum
        data['total_amount_sum'] = total_amount_sum
        return Response(data, status=status.HTTP_200_OK)

    def export_statistical_settlement(self, request):
        time_min = self.request.query_params.get('time_min', None)
        time_max = self.request.query_params.get('time_max', None)
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if not all([time_min, time_max]):
            return Response({'error': '缺少时间区间参数!'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            time_min1 = int(time.mktime(time.strptime(str(time_min) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
            time_max1 = int(time.mktime(time.strptime(str(time_max) + ' 00:00:00', "%Y-%m-%d %H:%M:%S")))
        except:
            return Response({'error': '时间区间参数有误!'}, status=status.HTTP_400_BAD_REQUEST)
        time_min1 = time_min + ' 00:00:00'
        time_max1 = time_max + ' 23:59:59'
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = ''
        elif level.level == 1:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'AND level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''
        SELECT DATE_FORMAT(settlement_time,'%Y-%m-%d')AS time,SUM(CASE channel_id WHEN 3 THEN amount ELSE 0 END)AS sxf_amount,SUM(CASE channel_id WHEN 2 THEN amount ELSE 0 END)AS ryx_amount,SUM(amount)AS total_amount FROM tb_merchant_settlement_record WHERE settlement_time>="{time_min1}" AND settlement_time<="{time_max1}" AND status=3 {condition} GROUP BY time;
        '''
        cursor.execute(sql)
        ziduan = ['日期', '机构名称', '总结算金额', '瑞银信结算金额', '随行付结算金额']
        cols = ['time', 'name', 'total_amount', 'ryx_amount', 'sxf_amount']
        response = StreamingHttpResponse(self.get_statistical_settlement_fromat(cols, cursor, conn, ziduan, level.name))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('结算统计.csv'))
        return response

    def get_statistical_settlement_fromat(self, cols, cursor, conn, ziduan, level_name):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'name':
                    tmp_str += '"%s",' % (level_name)
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
            yield tmp_str.strip(',') + "\n"


class SYOrderInfoModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = SYOrderInfoFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('shop_order_id', )

    def get_queryset(self):
        queryset = SYOrderInfo.objects.all()
        return queryset.order_by('-create_time')

    def get_serializer_class(self):
        if self.action == 'list':
            return SYOrderInfoListModelSerializer
        else:
            return SYOrderInfoRetrieveModelSerializer


class CustomizeActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('activity_name',)
    filter_class = CustomizeActivityFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        prize_type = self.request.query_params.get('prize_type', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CustomizeActivity.objects.all()
        elif level.level == 1:
            queryset = CustomizeActivity.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CustomizeActivity.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CustomizeActivity.objects.filter(level__district_id=level.id)
        else:
            queryset = CustomizeActivity.objects.filter(level__branch_id=level.id)
        if prize_type:
            try:
                prize_type = prize_type.split(',')
                if len(prize_type) == 1:
                    queryset = queryset.filter(prize_type__contains=prize_type[0])
                elif len(prize_type) == 2:
                    queryset = queryset.filter(Q(prize_type__contains=prize_type[0]) | Q(prize_type__contains=prize_type[1]))
                elif len(prize_type) == 3:
                    queryset = queryset.filter(Q(prize_type__contains=prize_type[0]) | Q(prize_type__contains=prize_type[1]) | Q(prize_type__contains=prize_type[2]))
            except:
                pass
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return CustomizeActivityListModelSerializer
        else:
            return CustomizeActivityRetrieveModelSerializer

    def create(self, request, *args, **kwargs):
        user = self.request.iser
        level_id = self.request.data.get('level_id', None)
        activity_name = self.request.data.get('activity_name', None)
        available_begin_time = self.request.data.get('available_begin_time', None)
        available_end_time = self.request.data.get('available_end_time', None)
        activity_description = self.request.data.get('activity_description', None)
        activity_type = self.request.data.get('activity_type', None)
        prize_type = self.request.data.get('prize_type', None)
        lucky_count_by_user = self.request.data.get('lucky_count_by_user', None)
        total_count = self.request.data.get('total_count', 0)
        is_business_form = self.request.data.get('is_business_form', 0)
        max_number = self.request.data.get('max_number', None)
        drawdown_platform_id = self.request.data.get('drawdown_platform_id', '3')
        if not all([activity_type, prize_type, level_id, activity_name, available_begin_time, available_end_time, activity_description, lucky_count_by_user]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            lucky_count_by_user = int(lucky_count_by_user)
        except:
            return Response({'error': '参与次数必须为正整数'}, status=status.HTTP_400_BAD_REQUEST)
        if str(activity_type) not in ['1', '2', '3', '4', '5', '6', '11']:
            return Response({'error': '活动类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            level = Level.objects.get(id=level_id)
        except:
            return Response({'error': '无效层级id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            available_begin_time = datetime.datetime.strptime(available_begin_time, '%Y-%m-%d %H:%M:%S')
            available_end_time = datetime.datetime.strptime(available_end_time, '%Y-%m-%d %H:%M:%S')
        except:
            return Response({'error': '活动时间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        if available_end_time < datetime.datetime.now():
            return Response({'error': '活动结束时间须大于当前时间'}, status=status.HTTP_400_BAD_REQUEST)
        if available_begin_time > available_end_time:
            return Response({'error': '活动开始时间需小于结束时间'}, status=status.HTTP_400_BAD_REQUEST)
        if is_business_form:
            if str(is_business_form) not in ['0', '1']:
                return Response({'error': '是否填写业务表单参数有误'}, status=status.HTTP_400_BAD_REQUEST)
        if str(activity_type) == '1':
            total_count = 9
            if lucky_count_by_user > total_count:
                return Response({'error': '随机抽奖类型活动最大参与数为9'}, status=status.HTTP_400_BAD_REQUEST)
        elif str(activity_type) == '2':
            total_count = 6
        elif str(activity_type) == '4':
            total_count = 8
        try:
            prize_type1 = prize_type.split(',')
            for i in prize_type1:
                if i not in '1,2,3,4,5,6,7,8':
                    return Response({'error': '奖品类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        except:
            return Response({'error': '奖品类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        if str(activity_type) == '11':
            if not max_number:
                return Response({'error': '员工派券包活动请上传活动最大数量'}, status=status.HTTP_400_BAD_REQUEST)
            if str(drawdown_platform_id) not in ['3', '4']:
                return Response({'error': '派发平台有误'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            try:
                customize_activity_data = {}
                customize_activity_data['activity_name'] = activity_name
                customize_activity_data['activity_description'] = activity_description
                customize_activity_data['activity_type'] = activity_type
                customize_activity_data['prize_type'] = prize_type
                customize_activity_data['level_id'] = level_id
                customize_activity_data['available_begin_time'] = available_begin_time
                customize_activity_data['available_end_time'] = available_end_time
                customize_activity_data['create_user_id'] = user.id
                customize_activity_data['lucky_count_by_user'] = lucky_count_by_user
                customize_activity_data['total_count'] = total_count
                customize_activity_data['is_business_form'] = is_business_form
                if str(activity_type) == '11':
                    customize_activity_data['max_number'] = max_number
                    customize_activity_data['remain_number'] = max_number
                    customize_activity_data['take_number'] = 0
                identify = random_str(48)
                common_url = f'https://api.sutpay.com/customer/customize/?identify={identify}&activity_type={activity_type}'
                common_qrcode = f'youhuiquanyi/customizeactivitylevel/{identify}.png'
                common_qrcode_url = make_qrcode2(identify, common_url, common_qrcode)
                customize_activity_data['identify'] = identify
                customize_activity_data['common_qrcode'] = common_qrcode
                customize_activity_data['common_url'] = common_url
                customize_activity_data['drawdown_platform_id'] = drawdown_platform_id
                customize_activity = CustomizeActivity.objects.create(**customize_activity_data)
                activity_id = f'CTA{str(customize_activity.id).zfill(8)}'
                customize_activity.activity_id = activity_id
                customize_activity.save()
                transaction.savepoint_commit(save_id)
                return Response({'success': '创建成功', 'id': customize_activity.id}, status=status.HTTP_200_OK)
            except Exception as e:
                transaction.savepoint_rollback(save_id)
                return Response({'error': '创建失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def update(self, request, *args, **kwargs):
        instance = self.get_object()
        is_need_auth = self.request.data.get('is_need_auth', None)
        is_white = self.request.data.get('is_white', None)
        available_begin_time = self.request.data.get('available_begin_time', None)
        available_end_time = self.request.data.get('available_end_time', None)
        activity_description = self.request.data.get('activity_description', None)
        activity_status = self.request.data.get('activity_status', None)
        if available_begin_time and available_end_time:
            try:
                available_begin_time = datetime.datetime.strptime(available_begin_time, '%Y-%m-%d %H:%M:%S')
                available_end_time = datetime.datetime.strptime(available_end_time, '%Y-%m-%d %H:%M:%S')
            except:
                return Response({'error': '活动时间参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
            if available_end_time < datetime.datetime.now():
                return Response({'error': '活动结束时间须大于当前时间'}, status=status.HTTP_400_BAD_REQUEST)
            if available_begin_time > available_end_time:
                return Response({'error': '活动开始时间需小于结束时间'}, status=status.HTTP_400_BAD_REQUEST)
            instance.available_begin_time = available_begin_time
            instance.available_end_time = available_end_time
        if activity_description:
            instance.activity_description = activity_description
        if activity_status:
            if str(activity_status) == '1':
                if instance.activity_status == 2:
                    return Response({'error': '已结束活动不得开启'}, status=status.HTTP_400_BAD_REQUEST)
                instance.activity_status = 1
            elif str(activity_status) == '2':
                if instance.activity_status != 1:
                    return Response({'error': '只有进行中的活动可以暂停'}, status=status.HTTP_400_BAD_REQUEST)
                instance.activity_status = 4
        if str(is_need_auth) == '0':
            instance.is_need_auth = 0
        elif str(is_need_auth) == '1':
            instance.is_need_auth = 1
        if str(is_white) == '0':
            instance.is_white = 0
        elif str(is_white) == '1':
            instance.is_white = 1
        try:
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '操作失败'}, status=status.HTTP_400_BAD_REQUEST)

    def partial_update(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.activity_status == 2:
            return Response({'error': '已结束活动不得绑定活动'}, status=status.HTTP_400_BAD_REQUEST)
        activity_array = self.request.data.get('activity_array', None)
        if not activity_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            try:
                if instance.activity_type == 1:
                    customizeactivityrules_set = instance.customizeactivityrules_set.all().aggregate(Sum('count'))
                    if customizeactivityrules_set['count__sum']:
                        total_count = instance.total_count - customizeactivityrules_set['count__sum']
                    else:
                        total_count = instance.total_count
                    for activity in activity_array:
                        if 'prize_type' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少奖品类型'}, status=status.HTTP_400_BAD_REQUEST)
                        if 'activity_id' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少活动id'}, status=status.HTTP_400_BAD_REQUEST)
                        if 'count' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少活动中奖次数'}, status=status.HTTP_400_BAD_REQUEST)
                        try:
                            count = int(activity['count'])
                        except:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数有误,活动中奖次数须为正整数'}, status=status.HTTP_400_BAD_REQUEST)
                        total_count -= count
                        if total_count < 0:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数有误,活动中奖次数须不能大于活动总奖项'}, status=status.HTTP_400_BAD_REQUEST)
                        if str(activity['prize_type']) in ['1', '2']:
                            alidiscountcoupon = AliDiscountCoupon.objects.filter(id=activity['activity_id']).first()
                            if not alidiscountcoupon:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '无效活动参数'}, status=status.HTTP_400_BAD_REQUEST)
                            if alidiscountcoupon.coupon_way != 6:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '活动类型不支持'}, status=status.HTTP_400_BAD_REQUEST)
                            alidiscountcouponchannelcommonvoucher = alidiscountcoupon.alidiscountcouponchannelcommonvoucher
                            if alidiscountcouponchannelcommonvoucher.join_limit_by_user == 1:
                                if count > instance.lucky_count_by_user:
                                    if instance.lucky_count_by_user > alidiscountcouponchannelcommonvoucher.join_limit_by_user_count:
                                        transaction.savepoint_rollback(save_id)
                                        return Response({'error': '活动奖品个数大于奖品参与限制'}, status=status.HTTP_400_BAD_REQUEST)
                                else:
                                    if count > alidiscountcouponchannelcommonvoucher.join_limit_by_user_count:
                                        transaction.savepoint_rollback(save_id)
                                        return Response({'error': '活动奖品个数大于奖品参与限制'}, status=status.HTTP_400_BAD_REQUEST)
                            customize_activity_rules_data = {}
                            customize_activity_rules_data['prize_type'] = activity['prize_type']
                            customize_activity_rules_data['count'] = count
                            customize_activity_rules_data['alidiscountcoupon_id'] = alidiscountcoupon.id
                            customize_activity_rules_data['customizeactivity_id'] = instance.id
                            CustomizeActivityRules.objects.get_or_create(defaults=customize_activity_rules_data, customizeactivity_id=instance.id, alidiscountcoupon_id=alidiscountcoupon.id)
                        elif str(activity['prize_type']) == '3':
                            cashactivity = CashActivity.objects.filter(id=activity['activity_id']).first()
                            if not cashactivity:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '无效活动参数'}, status=status.HTTP_400_BAD_REQUEST)
                            if cashactivity.activity_type != 7:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '活动类型不支持'}, status=status.HTTP_400_BAD_REQUEST)
                            customize_activity_rules_data = {}
                            customize_activity_rules_data['prize_type'] = activity['prize_type']
                            customize_activity_rules_data['count'] = activity['count']
                            customize_activity_rules_data['cashactivity_id'] = cashactivity.id
                            customize_activity_rules_data['customizeactivity_id'] = instance.id
                            CustomizeActivityRules.objects.get_or_create(defaults=customize_activity_rules_data, customizeactivity_id=instance.id, cashactivity_id=cashactivity.id)
                        else:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '奖品类型有误'}, status=status.HTTP_400_BAD_REQUEST)
                elif instance.activity_type in [2, 4]:
                    customizeactivityrules_set = instance.customizeactivityrules_set.all()
                    if customizeactivityrules_set:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '不可重复配置'}, status=status.HTTP_400_BAD_REQUEST)
                    if len(activity_array) != instance.total_count:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '参数格式有误,请上传对应扇叶数奖品'}, status=status.HTTP_400_BAD_REQUEST)
                    total_count = 100
                    for activity in activity_array:
                        if 'prize_type' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少奖品类型'}, status=status.HTTP_400_BAD_REQUEST)
                        if str(activity['prize_type']) in ['1', '2', '3']:
                            if 'activity_id' not in activity:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '参数格式有误,缺少活动id'}, status=status.HTTP_400_BAD_REQUEST)
                        if 'count' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少活动中奖次数'}, status=status.HTTP_400_BAD_REQUEST)
                        try:
                            count = int(activity['count'])
                        except:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数有误,活动中奖次数须为正整数'}, status=status.HTTP_400_BAD_REQUEST)
                        total_count -= count
                        if total_count < 0:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '随机概率值不得大于100'}, status=status.HTTP_400_BAD_REQUEST)
                        if str(activity['prize_type']) in ['1', '2']:
                            alidiscountcoupon = AliDiscountCoupon.objects.filter(id=activity['activity_id']).first()
                            if not alidiscountcoupon:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '无效活动参数'}, status=status.HTTP_400_BAD_REQUEST)
                            if alidiscountcoupon.coupon_way != 6:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '活动类型不支持'}, status=status.HTTP_400_BAD_REQUEST)
                            alidiscountcouponchannelcommonvoucher = alidiscountcoupon.alidiscountcouponchannelcommonvoucher
                            if alidiscountcouponchannelcommonvoucher.join_limit_by_user == 1:
                                if count > instance.lucky_count_by_user:
                                    if instance.lucky_count_by_user > alidiscountcouponchannelcommonvoucher.join_limit_by_user_count:
                                        transaction.savepoint_rollback(save_id)
                                        return Response({'error': '活动奖品个数大于奖品参与限制'}, status=status.HTTP_400_BAD_REQUEST)
                                else:
                                    if count > alidiscountcouponchannelcommonvoucher.join_limit_by_user_count:
                                        transaction.savepoint_rollback(save_id)
                                        return Response({'error': '活动奖品个数大于奖品参与限制'}, status=status.HTTP_400_BAD_REQUEST)
                            customize_activity_rules_data = {}
                            customize_activity_rules_data['prize_type'] = activity['prize_type']
                            customize_activity_rules_data['count'] = count
                            customize_activity_rules_data['alidiscountcoupon_id'] = alidiscountcoupon.id
                            customize_activity_rules_data['customizeactivity_id'] = instance.id
                            CustomizeActivityRules.objects.create(**customize_activity_rules_data)
                        elif str(activity['prize_type']) == '3':
                            cashactivity = CashActivity.objects.filter(id=activity['activity_id']).first()
                            if not cashactivity:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '无效活动参数'}, status=status.HTTP_400_BAD_REQUEST)
                            if cashactivity.activity_type != 7:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '活动类型不支持'}, status=status.HTTP_400_BAD_REQUEST)
                            customize_activity_rules_data = {}
                            customize_activity_rules_data['prize_type'] = activity['prize_type']
                            customize_activity_rules_data['count'] = activity['count']
                            customize_activity_rules_data['cashactivity_id'] = cashactivity.id
                            customize_activity_rules_data['customizeactivity_id'] = instance.id
                            CustomizeActivityRules.objects.create(**customize_activity_rules_data)
                        elif str(activity['prize_type']) == '4':
                            customize_activity_rules_data = {}
                            customize_activity_rules_data['prize_type'] = activity['prize_type']
                            customize_activity_rules_data['count'] = activity['count']
                            customize_activity_rules_data['customizeactivity_id'] = instance.id
                            CustomizeActivityRules.objects.create(**customize_activity_rules_data)
                        else:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '奖品类型有误'}, status=status.HTTP_400_BAD_REQUEST)
                elif instance.activity_type == 11:
                    for activity in activity_array:
                        if 'prize_type' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少奖品类型'}, status=status.HTTP_400_BAD_REQUEST)
                        if 'activity_id' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少活动id'}, status=status.HTTP_400_BAD_REQUEST)
                        if 'count' not in activity:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数格式有误,缺少活动中奖次数'}, status=status.HTTP_400_BAD_REQUEST)
                        try:
                            count = int(activity['count'])
                        except:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '参数有误,活动中奖次数须为正整数'}, status=status.HTTP_400_BAD_REQUEST)
                        if str(activity['prize_type']) in ['2', '8']:
                            alidiscountcoupon = AliDiscountCoupon.objects.filter(id=activity['activity_id']).first()
                            if not alidiscountcoupon:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '无效活动参数'}, status=status.HTTP_400_BAD_REQUEST)
                            if alidiscountcoupon.coupon_way != 6:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '活动类型不支持'}, status=status.HTTP_400_BAD_REQUEST)
                            if alidiscountcoupon.remain_number < count* instance.max_number:
                                transaction.savepoint_rollback(save_id)
                                return Response({'error': '券活动剩余数量不足券活动数'}, status=status.HTTP_400_BAD_REQUEST)
                            alidiscountcouponchannelcommonvoucher = alidiscountcoupon.alidiscountcouponchannelcommonvoucher
                            if alidiscountcouponchannelcommonvoucher.join_limit_by_user == 1:
                                if count > instance.lucky_count_by_user:
                                    if instance.lucky_count_by_user > alidiscountcouponchannelcommonvoucher.join_limit_by_user_count:
                                        transaction.savepoint_rollback(save_id)
                                        return Response({'error': '活动奖品个数大于奖品参与限制'}, status=status.HTTP_400_BAD_REQUEST)
                                else:
                                    if count > alidiscountcouponchannelcommonvoucher.join_limit_by_user_count:
                                        transaction.savepoint_rollback(save_id)
                                        return Response({'error': '活动奖品个数大于奖品参与限制'}, status=status.HTTP_400_BAD_REQUEST)
                            customize_activity_rules_data = {}
                            customize_activity_rules_data['prize_type'] = activity['prize_type']
                            customize_activity_rules_data['count'] = count
                            customize_activity_rules_data['alidiscountcoupon_id'] = alidiscountcoupon.id
                            customize_activity_rules_data['customizeactivity_id'] = instance.id
                            CustomizeActivityRules.objects.get_or_create(defaults=customize_activity_rules_data, customizeactivity_id=instance.id, alidiscountcoupon_id=alidiscountcoupon.id)
                        else:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '奖品类型有误'}, status=status.HTTP_400_BAD_REQUEST)
                else:
                    transaction.savepoint_rollback(save_id)
                    return Response({'error': '活动类型有误,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
                transaction.savepoint_commit(save_id)
                return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
            except Exception as e:
                transaction.savepoint_rollback(save_id)
                return Response({'error': '操作失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def allocation_customize(self, request):
        user = self.request.iser
        data_array = self.request.data.get('data_array', None)
        customizeactivity_id = self.request.data.get('customizeactivity_id', None)
        try:
            instance = self.get_queryset().get(id=customizeactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        if not data_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_status == 2:
            return Response({'error': '已结束活动不可操作'}, status=status.HTTP_400_BAD_REQUEST)
        if not isinstance(data_array, list):
            return Response({'error': '参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        level_list = get_level_list(instance.level)
        if instance.activity_type != 11:
            with transaction.atomic():
                # 创建事务保存点
                save_id = transaction.savepoint()
                try:
                    for level_id in data_array:
                        if int(level_id) not in level_list:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '存在无权限机构'}, status=status.HTTP_400_BAD_REQUEST)
                        customizeactivitylevel_data = {}
                        customizeactivitylevel_data['level_id'] = level_id
                        customizeactivitylevel_data['customizeactivity_id'] = instance.id
                        identify = random_str()
                        common_url = f'https://api.sutpay.com/customer/customize/?identify={identify}&activity_type={instance.activity_type}'
                        common_qrcode = f'youhuiquanyi/customizeactivitylevel/{identify}.png'
                        common_qrcode_url = make_qrcode2(identify, common_url, common_qrcode)
                        customizeactivitylevel_data['identify'] = identify
                        customizeactivitylevel_data['common_url'] = common_url
                        customizeactivitylevel_data['common_qrcode'] = common_qrcode
                        customizeactivitylevel, create = CustomizeActivityLevel.objects.get_or_create(defaults=customizeactivitylevel_data, customizeactivity_id=instance.id, level_id=level_id)
                except Exception as e:
                    logger.error(e)
                    transaction.savepoint_rollback(save_id)
                    return Response({'error': '分配失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
                transaction.savepoint_commit(save_id)
                return Response({'success': '分配成功'}, status=status.HTTP_200_OK)
        else:
            with transaction.atomic():
                # 创建事务保存点
                save_id = transaction.savepoint()
                try:
                    total_number = 0
                    for data in data_array:
                        level_id = data['level_id']
                        max_number = int(data['max_number'])
                        if int(level_id) not in level_list:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '存在无权限机构'}, status=status.HTTP_400_BAD_REQUEST)
                        total_number += max_number
                    while True:
                        customizeactivity = CustomizeActivity.objects.get(id=customizeactivity_id)
                        origin_take_number = customizeactivity.take_number
                        origin_remain_number = customizeactivity.remain_number
                        if origin_remain_number < total_number:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '额度不足分配'}, status=status.HTTP_400_BAD_REQUEST)
                        new_take_number = origin_take_number + total_number
                        new_remain_number = origin_remain_number - total_number
                        result = CustomizeActivity.objects.filter(id=customizeactivity_id, take_number=origin_take_number, remain_number=origin_remain_number).update(take_number=new_take_number,remain_number=new_remain_number)
                        if result == 0:
                            continue
                        break
                    for data in data_array:
                        level_id = data['level_id']
                        max_number = int(data['max_number'])
                        customizeactivitylevel_data = {}
                        customizeactivitylevel_data['level_id'] = level_id
                        customizeactivitylevel_data['customizeactivity_id'] = instance.id
                        identify = random_str()
                        common_url = f'https://api.sutpay.com/customer/customize/?identify={identify}&activity_type={instance.activity_type}'
                        common_qrcode = f'youhuiquanyi/customizeactivitylevel/{identify}.png'
                        common_qrcode_url = make_qrcode2(identify, common_url, common_qrcode)
                        customizeactivitylevel_data['identify'] = identify
                        customizeactivitylevel_data['common_url'] = common_url
                        customizeactivitylevel_data['common_qrcode'] = common_qrcode
                        customizeactivitylevel_data['max_number'] = max_number
                        customizeactivitylevel_data['remain_number'] = max_number
                        customizeactivitylevel_data['take_number'] = 0
                        customizeactivitylevel_data['receive_number'] = 0
                        customizeactivitylevel, create = CustomizeActivityLevel.objects.get_or_create(defaults=customizeactivitylevel_data, customizeactivity_id=instance.id, level_id=level_id)
                except Exception as e:
                    logger.error(e)
                    transaction.savepoint_rollback(save_id)
                    return Response({'error': '分配失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
                transaction.savepoint_commit(save_id)
                return Response({'success': '分配成功'}, status=status.HTTP_200_OK)

    def allocation_customize_level(self, request):
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        search = self.request.query_params.get('search', None)
        if not customizeactivity_id:
            return Response({'error': '请传活动id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=customizeactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        level = instance.level
        if level.level == 0:
            queryset = Level.objects.all().order_by('level')
        elif level.level == 1:
            queryset = Level.objects.filter(prov_id=level.id).order_by('level')
        elif level.level == 2:
            queryset = Level.objects.filter(city_id=level.id).order_by('level')
        elif level.level == 3:
            queryset = Level.objects.filter(district_id=level.id).order_by('level')
        else:
            queryset = Level.objects.filter(branch_id=level.id).order_by('level')
        queryset = queryset.exclude(customizeactivitylevel__customizeactivity_id=instance.id)
        if search:
            queryset = queryset.filter(Q(name__contains=search) | Q(account__contains=search))
        page = self.paginate_queryset(queryset)
        if page is not None:
            serializer = LevelListSerializer(page, many=True)
            return self.get_paginated_response(serializer.data)
        serializer = LevelListSerializer(queryset, many=True)
        return Response(serializer.data)

    def update_customize_white_table(self, request):
        customizeactivity_id = self.request.data.get('customizeactivity_id', None)
        mobile_array = self.request.data.get('mobile_array', None)
        try:
            instance = self.get_queryset().get(id=customizeactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            # 暴力回滚
            try:
                customizeactivitywhitetable_set = instance.customizeactivitywhitetable_set.all().delete()
                for mobile in mobile_array:
                    if not re.match(r'^1[3-9]\d{9}$', mobile):
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': f'{mobile}非手机号格式'}, status=status.HTTP_400_BAD_REQUEST)
                    CustomizeActivityWhiteTable.objects.create(customizeactivity_id=customizeactivity_id, mobile=mobile)
                transaction.savepoint_commit(save_id)
                return Response({'success': '修改成功'}, status=status.HTTP_200_OK)
            except Exception as e:
                logger.error(e)
                # 事务回滚
                transaction.savepoint_rollback(save_id)
                return Response({'error': '修改失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def customize_white_table(self, request):
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        try:
            instance = self.get_queryset().get(id=customizeactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        customizeactivitywhitetable_set = instance.customizeactivitywhitetable_set.all().values_list('mobile', flat=True)
        return Response({'data': list(customizeactivitywhitetable_set)}, status=status.HTTP_200_OK)


class CustomizeActivityRulesModelViewSet(ModelViewSet):

    pagination_class = MyPage
    serializer_class = CustomizeActivityRulesListModelSerializer

    def get_queryset(self):
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        queryset = CustomizeActivityRules.objects.filter(customizeactivity_id=customizeactivity_id)
        return queryset


class CustomizeActivityLevelModelViewSet(ModelViewSet):

    pagination_class = MyPage
    search_fields = ('level__name', )
    filter_class = CustomizeActivityLevelFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CustomizeActivityLevel.objects.all()
        elif level.level == 1:
            queryset = CustomizeActivityLevel.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CustomizeActivityLevel.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CustomizeActivityLevel.objects.filter(level__district_id=level.id)
        else:
            queryset = CustomizeActivityLevel.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return CustomizeActivityLevelListModelSerializer


class UpdateStockUsableMerchantModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = UpdateStockUsableMerchantFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('customer_user__mobile', )
    queryset = UpdateStockUsableMerchant.objects.exclude(add_type=3).order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return UpdateStockUsableMerchantListModelSerializer
        else:
            return UpdateStockUsableMerchantRetrieveModelSerializer

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        apply_state = str(self.request.data.get('apply_state', None))
        return_msg = self.request.data.get('return_msg', None)
        is_wx = str(self.request.data.get('is_wx', None))
        is_ylx = str(self.request.data.get('is_ylx', None))
        if apply_state not in ['1', '0']:
            return Response({'error': '无效审核参数'}, status=status.HTTP_400_BAD_REQUEST)
        instance = self.get_object()
        if instance.apply_state != 1:
            return Response({'error': '申请单状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        if apply_state == '0':
            if not return_msg:
                return Response({'error': '请提交驳回原因'}, status=status.HTTP_400_BAD_REQUEST)
            instance.return_msg = return_msg
            instance.apply_state = 3
            instance.wx_is_enter = 5
            instance.ylx_is_enter = 5
            instance.operation_user_id = user.id
            instance.operation_time = datetime.datetime.now()
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        else:
            if is_wx not in ['1', '0']:
                return Response({'error': '无效微信路径参数'}, status=status.HTTP_400_BAD_REQUEST)
            if is_ylx not in ['1', '0']:
                return Response({'error': '无效邮乐享路径参数'}, status=status.HTTP_400_BAD_REQUEST)
            if user.platform == 2:
                check_history = UpdateStockUsableMerchant.objects.filter(operation_user__platform=2, wx_is_enter=1)
            else:
                check_history = UpdateStockUsableMerchant.objects.filter(operation_user__platform=1, wx_is_enter=1)
            if check_history:
                is_enter = 4
            else:
                is_enter = 1
            instance.apply_state = 2
            instance.wx_is_enter = is_enter
            instance.ylx_is_enter = is_enter
            instance.is_wx = is_wx
            instance.is_ylx = is_ylx
            instance.operation_user_id = user.id
            instance.operation_time = datetime.datetime.now()
            instance.save()
            if is_enter == 1:
                update_stock_usable_merchant.delay({'id': instance.id})
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)


class AliDiscountCouponMerchantModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = AliDiscountCouponMerchantFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('merchant__name', 'merchant__wyfMerchantID', 'sub_mchid')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = AliDiscountCouponMerchant.objects.all()
        elif level.level == 1:
            queryset = AliDiscountCouponMerchant.objects.filter(merchant__level__prov_id=level.id)
        elif level.level == 2:
            queryset = AliDiscountCouponMerchant.objects.filter(merchant__level__city_id=level.id)
        elif level.level == 3:
            queryset = AliDiscountCouponMerchant.objects.filter(merchant__level__district_id=level.id)
        else:
            queryset = AliDiscountCouponMerchant.objects.filter(merchant__level__branch_id=level.id)
        return queryset

    def get_serializer_class(self):
        return AliDiscountCouponMerchantModelSerializer


class CashActivityModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('name', )
    filter_class = CashActivityFilter

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        customizeactivity_id = self.request.query_params.get('customizeactivity_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CashActivity.objects.all()
        elif level.level == 1:
            queryset = CashActivity.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CashActivity.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CashActivity.objects.filter(level__district_id=level.id)
        else:
            queryset = CashActivity.objects.filter(level__branch_id=level.id)
        if customizeactivity_id:
            queryset = queryset.exclude(customizeactivityrules__customizeactivity_id=customizeactivity_id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        if self.action == 'list':
            return CashActivityListModelSerializer
        else:
            return CashActivityRetrieveModelSerializer

    def partial_update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        activity_state = self.request.data.get('activity_state', '')
        is_max_amount_by_day = self.request.data.get('is_max_amount_by_day', '')
        is_max_amount_by_user_by_day = self.request.data.get('is_max_amount_by_user_by_day', '')
        is_max_amount_by_user = self.request.data.get('is_max_amount_by_user', '')
        is_max_amount_by_customeruser = self.request.data.get('is_max_amount_by_customeruser', '')
        max_amount_by_day = self.request.data.get('max_amount_by_day', '')
        max_amount_by_user_by_day = self.request.data.get('max_amount_by_user_by_day', '')
        max_amount_by_user = self.request.data.get('max_amount_by_user', '')
        max_amount_by_customeruser = self.request.data.get('max_amount_by_customeruser', '')
        activity_description = self.request.data.get('activity_description', '')
        available_begin_time = self.request.data.get('available_begin_time', '')
        available_end_time = self.request.data.get('available_end_time', '')
        is_need_auth = self.request.data.get('is_need_auth', '')
        is_check_identity = self.request.data.get('is_check_identity', '')
        wx_channel_id = self.request.data.get('wx_channel_id', 9)
        ali_channel_id = self.request.data.get('ali_channel_id', None)
        is_push_ylx = self.request.data.get('is_push_ylx', '')
        is_psbc_card = self.request.data.get('is_psbc_card', '')
        is_wechat = self.request.data.get('is_wechat', '')
        is_white = self.request.data.get('is_white', '')
        is_merchant = self.request.data.get('is_merchant', '')
        is_limit_white = self.request.data.get('is_limit_white', '')
        is_white_merchant = self.request.data.get('is_white_merchant', '')
        white_accout = self.request.data.get('white_accout', '')
        repeat_business_no_count = self.request.data.get('repeat_business_no_count', '')
        is_keguan = self.request.data.get('is_keguan', '')
        pushEventIds = self.request.data.get('pushEventIds', '')
        if str(is_keguan) == '1':
            if not pushEventIds:
                return Response({'error': '请上传营销事件ID'}, status=status.HTTP_400_BAD_REQUEST)
            if not instance.level.account_nine:
                return Response({'error': '活动所属机构尚未绑定9位机构号,请联系管理员处理'}, status=status.HTTP_400_BAD_REQUEST)
            instance.is_keguan = 1
            instance.pushEventIds = pushEventIds
            keguan_data = {}
            keguan_data['sourceId'] = instance.activity_id
            keguan_data['pushEventIds'] = str(pushEventIds).split('|')
            keguan_data['orgNo'] = instance.level.account_nine
            keguan_data['activityName'] = instance.name
            keguan_data['startTime'] = instance.available_begin_time.strftime("%Y-%m-%dT%H:%M:%S.120+08:00")
            keguan_data['endTime'] = instance.available_end_time.strftime("%Y-%m-%dT%H:%M:%S.120+08:00")
            keguan_result = ylx_api('ThirdActInfoSyncGdCrm', keguan_data)
            if keguan_result['meta']['code'] != 200:
                return Response({'error': f"同步客管系统失败,原因:{keguan_result['meta']['msg']}"}, status=status.HTTP_400_BAD_REQUEST)
            CashActivityKeguanRecords.objects.create(cashactivity_id=instance.id, operate_user_id=user.id, operate_type=1)
        elif str(is_keguan) == '0':
            instance.is_keguan = 0
            CashActivityKeguanRecords.objects.create(cashactivity_id=instance.id, operate_user_id=user.id, operate_type=2)
        if activity_state:
            if instance.activity_state == 4:
                return Response({'error': '活动已结束, 不可操作'}, status=status.HTTP_400_BAD_REQUEST)
            if str(activity_state) == '1':
                instance.activity_state = 2
                instance.save()
                return Response({'success': '开启活动成功'}, status=status.HTTP_200_OK)
            elif str(activity_state) == '2':
                instance.activity_state = 3
                instance.save()
                return Response({'success': '暂停活动成功'}, status=status.HTTP_200_OK)
            else:
                return Response({'error': '无效活动状态'}, status=status.HTTP_400_BAD_REQUEST)
        if repeat_business_no_count:
            try:
                repeat_business_no_count = int(repeat_business_no_count)
            except:
                return Response({'error': '重复商户数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
            if repeat_business_no_count < 1:
                return Response({'error': '重复商户数不能小于1'}, status=status.HTTP_400_BAD_REQUEST)
            instance.repeat_business_no_count = repeat_business_no_count
        if str(is_white_merchant) == '1':
            instance.is_white_merchant = 1
        if str(is_white_merchant) == '0':
            instance.is_white_merchant = 0
        if str(is_push_ylx) == '1':
            instance.is_push_ylx = 1
        if str(is_push_ylx) == '0':
            instance.is_push_ylx = 0
        if str(is_merchant) == '1':
            instance.is_merchant = 1
        if str(is_merchant) == '0':
            instance.is_merchant = 0
        if str(is_white) == '1':
            instance.is_white = 1
        if str(is_white) == '0':
            instance.is_white = 0
        if str(is_limit_white) == '1':
            instance.is_limit_white = 1
        if str(is_limit_white) == '0':
            instance.is_limit_white = 0
        if str(is_wechat) == '1':
            instance.is_wechat = 1
        if str(is_wechat) == '0':
            instance.is_wechat = 0
        if str(is_psbc_card) in ['0', '1']:
            instance.is_psbc_card = is_psbc_card
        if str(white_accout):
            instance.white_accout = white_accout
        if str(is_max_amount_by_day):
            instance.is_max_amount_by_day = is_max_amount_by_day
        if max_amount_by_day:
            instance.max_amount_by_day = max_amount_by_day
        if ali_channel_id:
            instance.ali_channel_id = ali_channel_id
        if str(is_max_amount_by_user_by_day):
            instance.is_max_amount_by_user_by_day = is_max_amount_by_user_by_day
        if max_amount_by_user_by_day:
            instance.max_amount_by_user_by_day = max_amount_by_user_by_day
        if str(is_max_amount_by_user):
            instance.is_max_amount_by_user = is_max_amount_by_user
        if max_amount_by_user:
            instance.max_amount_by_user = max_amount_by_user
        if str(is_max_amount_by_customeruser):
            instance.is_max_amount_by_customeruser = is_max_amount_by_customeruser
        if max_amount_by_customeruser:
            instance.max_amount_by_customeruser = max_amount_by_customeruser
        if activity_description:
            instance.activity_description = activity_description
        if available_begin_time and available_end_time:
            try:
                available_begin_time = datetime.datetime.strptime(available_begin_time, '%Y-%m-%d %H:%M:%S')
                available_end_time = datetime.datetime.strptime(available_end_time, '%Y-%m-%d %H:%M:%S')
            except:
                return Response({'error': '时间格式有误'}, status=status.HTTP_400_BAD_REQUEST)
            instance.available_begin_time = available_begin_time
            instance.available_end_time = available_end_time
            if available_end_time > datetime.datetime.now() and instance.activity_state == 4:
                try:
                    conn = pymysql.connect(host=settings.DATABASES['fnonline']['HOST'],
                                           port=settings.DATABASES['fnonline']['PORT'],
                                           database=settings.DATABASES['fnonline']['NAME'],
                                           user=settings.DATABASES['fnonline']['USER'],
                                           password=settings.DATABASES['fnonline']['PASSWORD'])
                    cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
                    sql = f'''SELECT c.*,d.u_rpbalance FROM (SELECT a.*,b.rpbalance FROM (SELECT id,activity_status,owner_id,area_id,settle_amount,order_id FROM z_platform_activity WHERE template_id="{instance.activity_id}")AS a LEFT JOIN (SELECT id,rpbalance FROM orders_arealevel)AS b ON a.area_id=b.id)AS c LEFT JOIN (SELECT id,rpbalance AS u_rpbalance FROM users_users)AS d ON c.owner_id=d.id;'''
                    cursor.execute(sql)
                    row = cursor.fetchone()
                    if row['activity_status'] == 3:
                        datetime_now = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
                        owner_id = row['owner_id']
                        order_id = row['order_id']
                        area_id = row['area_id']
                        settle_amount = row['settle_amount']
                        rpbalance = row['rpbalance']
                        u_rpbalance = row['u_rpbalance']
                        a_id = row['id']
                        if rpbalance < settle_amount:
                            return Response({'error': '余额不足抵扣,不能续期'}, status=status.HTTP_400_BAD_REQUEST)
                        sql = f'''SELECT id FROM users_users WHERE mobile="{user.mobile}";'''
                        cursor.execute(sql)
                        row = cursor.fetchone()
                        createuser_id = row['id']
                        if not createuser_id:
                            return Response({'error': '你暂无权限操作续期'}, status=status.HTTP_400_BAD_REQUEST)
                        area_balance = rpbalance - settle_amount
                        balance = u_rpbalance - settle_amount
                        sql = f'''UPDATE users_users SET rpbalance={balance} WHERE id={owner_id};'''
                        cursor.execute(sql)
                        conn.commit()
                        sql = f'''UPDATE orders_arealevel SET rpbalance={area_balance} WHERE id={area_id};'''
                        cursor.execute(sql)
                        conn.commit()
                        sql = f'''UPDATE z_platform_activity SET activity_status=1,settle_amount=0 WHERE id={a_id};'''
                        cursor.execute(sql)
                        conn.commit()
                        sql = f'''INSERT INTO recharge_wyhbalance(area_id,money,b_type,user_id,remark,source,create_time) VALUES ({area_id},{settle_amount},2,{owner_id},"活动续期",9,"{datetime_now}");'''
                        cursor.execute(sql)
                        conn.commit()
                        sql = 'SELECT LAST_INSERT_ID() AS wyh_id;'
                        cursor.execute(sql)
                        row = cursor.fetchone()
                        wyh_id = row['wyh_id']
                        number = f'O{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                        sql = f'''INSERT INTO recharge_transaction(user_id,number,source,t_type,`order`,t_money,c_money,balance,area_id,addtime) VALUES ({owner_id},"{number}",9,21,{wyh_id},{settle_amount},{-settle_amount},{area_balance},{area_id},"{datetime_now}");'''
                        cursor.execute(sql)
                        conn.commit()
                        number = f'O{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}{"%06d" % randint(0, 999999)}'
                        sql = f'''INSERT INTO recharge_transaction(user_id,number,source,t_type,`order`,t_money,c_money,balance,addtime) VALUES ({owner_id},"{number}",9,16,{order_id},{settle_amount},{-settle_amount},{balance},"{datetime_now}");'''
                        cursor.execute(sql)
                        conn.commit()
                        sql = f'''INSERT INTO orders_advance(order_id,order_state,user_id,remark,advance_time) VALUES ({order_id},2,{createuser_id},"操作续期活动{instance.activity_id},扣回余额:{settle_amount}","{datetime_now}");'''
                        cursor.execute(sql)
                        conn.commit()
                    cursor.close()
                    conn.close()
                except:
                    return Response({'error': '修改失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
                instance.activity_state = 3
        if str(is_need_auth):
            instance.is_need_auth = is_need_auth
        if str(is_check_identity):
            instance.is_check_identity = is_check_identity
        try:
            instance.save()
            return Response({'success': '操作成功'}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '操作失败'}, status=status.HTTP_400_BAD_REQUEST)

    def allocation_max_amount(self, request):
        user = self.request.iser
        data_array = self.request.data.get('data_array', None)
        cash_activity_id = self.request.data.get('cash_activity_id', None)
        try:
            instance = self.get_queryset().get(id=cash_activity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        if not data_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_state == 4:
            return Response({'error': '已结束活动不可操作'}, status=status.HTTP_400_BAD_REQUEST)
        if not isinstance(data_array, list):
            return Response({'error': '参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        level_list = get_level_list(instance.level)
        level_range = instance.level.level
        max_amount__sum = instance.cashactivitylevel_set.all().aggregate(Sum('max_amount'))['max_amount__sum']
        if max_amount__sum:
            remain_amount = instance.max_amount - max_amount__sum
        else:
            remain_amount = instance.max_amount
        if instance.activity_type in [1, 2, 3]:
            with transaction.atomic():
                # 创建事务保存点
                save_id = transaction.savepoint()
                try:
                    queryset_list = []
                    for allot in data_array:
                        if int(allot['level_id']) not in level_list:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '存在无权限机构'}, status=status.HTTP_400_BAD_REQUEST)
                        if Decimal(allot['amount']) <= 0:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '分配数量须大于0'}, status=status.HTTP_400_BAD_REQUEST)
                        if remain_amount < Decimal(allot['amount']):
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '分配数据超过活动最大数'}, status=status.HTTP_400_BAD_REQUEST)
                        cashactivitylevel_data = {}
                        cashactivitylevel_data['level_id'] = allot['level_id']
                        cashactivitylevel_data['cash_activity_id'] = instance.id
                        cashactivitylevel = CashActivityLevel.objects.filter(cash_activity_id=instance.id, level_id=allot['level_id']).first()
                        if cashactivitylevel and cashactivitylevel.max_amount != 0:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': f'{cashactivitylevel.level.name}重复配置'}, status=status.HTTP_400_BAD_REQUEST)
                        if cashactivitylevel:
                            cashactivitylevel.max_amount += Decimal(allot['amount'])
                            cashactivitylevel.remain_amount += Decimal(allot['amount'])
                            cashactivitylevel.p_max_amount += Decimal(allot['amount'])
                            cashactivitylevel.p_remain_amount += Decimal(allot['amount'])
                            cashactivitylevel.save()
                        else:
                            cashactivitylevel_data['max_amount'] = Decimal(allot['amount'])
                            cashactivitylevel_data['remain_amount'] = Decimal(allot['amount'])
                            cashactivitylevel_data['p_max_amount'] = Decimal(allot['amount'])
                            cashactivitylevel_data['p_remain_amount'] = Decimal(allot['amount'])
                            cashactivitylevel_data['is_auth'] = 1
                            cashactivitylevel, create = CashActivityLevel.objects.get_or_create(defaults=cashactivitylevel_data, cash_activity_id=instance.id, level_id=allot['level_id'])
                        remain_amount -= Decimal(allot['amount'])
                        queryset_list.append(cashactivitylevel.id)
                        try:
                            cashactivitylevelrecord_data = {}
                            cashactivitylevelrecord_data['activity_level_id'] = cashactivitylevel.id
                            cashactivitylevelrecord_data['amount'] = allot['amount']
                            cashactivitylevelrecord_data['operation_type'] = 1
                            cashactivitylevelrecord_data['remain_amount'] = cashactivitylevel.remain_amount
                            cashactivitylevelrecord_data['p_remain_amount'] = cashactivitylevel.p_remain_amount
                            cashactivitylevelrecord_data['max_amount'] = cashactivitylevel.max_amount
                            cashactivitylevelrecord_data['p_max_amount'] = cashactivitylevel.p_max_amount
                            cashactivitylevelrecord_data['user_id'] = user.id
                            cashactivitylevelrecord_data['activity_id'] = instance.id
                            cashactivitylevelrecord_data['remark'] = '超管通过后台分配'
                            CashActivityLevelRecord.objects.create(**cashactivitylevelrecord_data)
                        except Exception as e:
                            print(e)
                            pass
                    level_level_list = []
                    queryset_list1 = []
                    for i in range(4, level_range, -1):
                        level_level_list.append(i)
                        queryset = CashActivityLevel.objects.filter(id__in=queryset_list, level__level__in=level_level_list)
                        for query in queryset:
                            query_level = query.level
                            if i == 4:
                                cashactivitylevel_level_id = query_level.parent_id
                            elif i == 3:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            elif i == 2:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent_id
                                elif query_level.level == 3:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            else:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent.parent_id
                                elif query_level.level == 3:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent_id
                                elif query_level.level == 2:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            instance1 = CashActivityLevel.objects.filter(level_id=cashactivitylevel_level_id, cash_activity_id=instance.id).first()
                            if not instance1:
                                instance1 = CashActivityLevel.objects.create(level_id=cashactivitylevel_level_id, cash_activity_id=instance.id, max_amount=0)
                            queryset_list1.append(instance1.id)
                            p_max_amount = instance1.p_max_amount + query.max_amount
                            p_remain_amount = instance1.p_remain_amount + query.remain_amount
                            instance1.p_max_amount = p_max_amount
                            instance1.p_remain_amount = p_remain_amount
                            instance1.save()
                            relation_data = {
                                "parent_id": instance1.id,
                                "child_id": query.id
                            }
                            CashActivityLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                    level_level_list = []
                    for i in range(4, level_range, -1):
                        level_level_list.append(i)
                        queryset = CashActivityLevel.objects.filter(id__in=queryset_list1, level__level__in=level_level_list)
                        for query in queryset:
                            query_level = query.level
                            if i == 4:
                                cashactivitylevel_level_id = query_level.parent_id
                            elif i == 3:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            elif i == 2:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent_id
                                elif query_level.level == 3:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            else:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent.parent_id
                                elif query_level.level == 3:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent_id
                                elif query_level.level == 2:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            instance1 = CashActivityLevel.objects.filter(level_id=cashactivitylevel_level_id, cash_activity_id=instance.id).first()
                            if not instance1:
                                instance1 = CashActivityLevel.objects.create(level_id=cashactivitylevel_level_id, cash_activity_id=instance.id, max_amount=0)
                            # queryset_list1.append(instance1.id)
                            # p_max_amount = instance1.p_max_amount + query.max_amount
                            # p_remain_amount = instance1.p_remain_amount + query.remain_amount
                            # instance1.p_max_amount = p_max_amount
                            # instance1.p_remain_amount = p_remain_amount
                            # instance1.save()
                            relation_data = {
                                "parent_id": instance1.id,
                                "child_id": query.id
                            }
                            CashActivityLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                except Exception as e:
                    logger.error(e)
                    transaction.savepoint_rollback(save_id)
                    return Response({'error': '分配失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
                transaction.savepoint_commit(save_id)
                return Response({'success': '分配成功'}, status=status.HTTP_200_OK)
        else:
            with transaction.atomic():
                # 创建事务保存点
                save_id = transaction.savepoint()
                try:
                    queryset_list = []
                    for allot in data_array:
                        if int(allot['level_id']) not in level_list:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '存在无权限机构'}, status=status.HTTP_400_BAD_REQUEST)
                        cashactivitylevel_data = {}
                        cashactivitylevel_data['level_id'] = allot['level_id']
                        cashactivitylevel_data['cash_activity_id'] = instance.id
                        cashactivitylevel = CashActivityLevel.objects.filter(cash_activity_id=instance.id, level_id=allot['level_id']).first()
                        # if cashactivitylevel and cashactivitylevel.max_amount != 0:
                        #     transaction.savepoint_rollback(save_id)
                        #     return Response({'error': f'{cashactivitylevel.level.name}重复配置'}, status=status.HTTP_400_BAD_REQUEST)
                        if cashactivitylevel:
                            if cashactivitylevel.is_auth != 1:
                                cashactivitylevel.is_auth = 1
                                cashactivitylevel.save()
                        else:
                            cashactivitylevel_data['is_auth'] = 1
                            cashactivitylevel, create = CashActivityLevel.objects.get_or_create(defaults=cashactivitylevel_data, cash_activity_id=instance.id, level_id=allot['level_id'])
                        queryset_list.append(cashactivitylevel.id)
                    level_level_list = []
                    for i in range(4, level_range, -1):
                        level_level_list.append(i)
                        queryset = CashActivityLevel.objects.filter(id__in=queryset_list, level__level__in=level_level_list)
                        for query in queryset:
                            query_level = query.level
                            if i == 4:
                                cashactivitylevel_level_id = query_level.parent_id
                            elif i == 3:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            elif i == 2:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent_id
                                elif query_level.level == 3:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            else:
                                if query_level.level == 4:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent.parent_id
                                elif query_level.level == 3:
                                    cashactivitylevel_level_id = query_level.parent.parent.parent_id
                                elif query_level.level == 2:
                                    cashactivitylevel_level_id = query_level.parent.parent_id
                                else:
                                    cashactivitylevel_level_id = query_level.parent_id
                            instance1 = CashActivityLevel.objects.filter(level_id=cashactivitylevel_level_id, cash_activity_id=instance.id).first()
                            if not instance1:
                                instance1 = CashActivityLevel.objects.create(level_id=cashactivitylevel_level_id, cash_activity_id=instance.id, max_amount=0)
                            queryset_list.append(instance1.id)
                            relation_data = {
                                "parent_id": instance1.id,
                                "child_id": query.id
                            }
                            CashActivityLevelRelation.objects.get_or_create(defaults=relation_data, parent_id=instance1.id, child_id=query.id)
                except Exception as e:
                    logger.error(e)
                    transaction.savepoint_rollback(save_id)
                    return Response({'error': '分配失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
                transaction.savepoint_commit(save_id)
                return Response({'success': '分配成功'}, status=status.HTTP_200_OK)

    def allocation_max_amount_level(self, request):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        search = self.request.query_params.get('search', None)
        if not cash_activity_id:
            return Response({'error': '请传活动id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            instance = self.get_queryset().get(id=cash_activity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            max_amount__sum = instance.cashactivitylevel_set.all().aggregate(Sum('max_amount'))['max_amount__sum']
            if max_amount__sum:
                remain_amount = instance.max_amount - max_amount__sum
            else:
                remain_amount = instance.max_amount
            can_remain_amount = remain_amount
        except:
            can_remain_amount = 0
        try:
            if '_mutable' in self.request.data.__dict__:
                self.request.data.__dict__['_mutable'] = True
        except:
            pass
        self.request.data['can_remain_amount'] = str(can_remain_amount)
        level = instance.level
        if level.level == 0:
            queryset = Level.objects.all().order_by('level')
        elif level.level == 1:
            queryset = Level.objects.filter(prov_id=level.id).order_by('level')
        elif level.level == 2:
            queryset = Level.objects.filter(city_id=level.id).order_by('level')
        elif level.level == 3:
            queryset = Level.objects.filter(district_id=level.id).order_by('level')
        else:
            queryset = Level.objects.filter(branch_id=level.id).order_by('level')
        queryset = queryset.exclude(cashactivitylevel__cash_activity_id=instance.id)
        if search:
            queryset = queryset.filter(Q(name__contains=search) | Q(account__contains=search))
        page = self.paginate_queryset(queryset)
        if page is not None:
            serializer = LevelListSerializer(page, many=True)
            return self.get_paginated_response(serializer.data)
        serializer = LevelListSerializer(queryset, many=True)
        return Response(serializer.data)

    def create(self, request, *args, **kwargs):
        user = self.request.iser
        activity_type = self.request.data.get('activity_type', '')
        max_amount = self.request.data.get('max_amount', '')
        level_id = self.request.data.get('level_id', '')
        name = self.request.data.get('name', '')
        available_begin_time = self.request.data.get('available_begin_time', '')
        available_end_time = self.request.data.get('available_end_time', '')
        activity_description = self.request.data.get('activity_description', '')
        max_distributed_amount = self.request.data.get('max_distributed_amount', '')
        min_distributed_amount = self.request.data.get('min_distributed_amount', '')
        cash_activity_rule = self.request.data.get('cash_activity_rule', '')
        wx_channel_id = self.request.data.get('wx_channel_id', 9)
        ali_channel_id = self.request.data.get('ali_channel_id', 1)
        max_amount_by_day = self.request.data.get('max_amount_by_day', None)
        max_amount_by_user_by_day = self.request.data.get('max_amount_by_user_by_day', None)
        max_amount_by_user = self.request.data.get('max_amount_by_user', None)
        max_amount_by_customeruser = self.request.data.get('max_amount_by_customeruser', None)
        if not all([activity_type, max_amount, level_id, name, available_begin_time, available_end_time, activity_description]):
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            max_amount = Decimal(max_amount)
        except:
            return Response({'error': '活动预算值格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            level = Level.objects.get(id=level_id)
        except:
            return Response({'error': '无效层级id'}, status=status.HTTP_400_BAD_REQUEST)
        if str(activity_type) not in ['1', '2', '3', '4', '5', '6', '7', '8']:
            return Response({'error': '活动类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            try:
                cash_activity_data = {}
                cash_activity_data['max_amount'] = max_amount
                cash_activity_data['remain_amount'] = max_amount
                cash_activity_data['activity_type'] = activity_type
                cash_activity_data['name'] = name
                cash_activity_data['level_id'] = level_id
                cash_activity_data['create_time'] = datetime.datetime.now()
                cash_activity_data['available_begin_time'] = available_begin_time
                cash_activity_data['available_end_time'] = available_end_time
                cash_activity_data['create_user_id'] = user.id
                cash_activity_data['activity_description'] = activity_description
                cash_activity_data['wx_channel_id'] = wx_channel_id
                cash_activity_data['ali_channel_id'] = ali_channel_id
                cash_activity_data['is_push_ylx'] = 0
                # cash_activity_data['max_amount_by_day'] = max_amount_by_day
                # cash_activity_data['max_amount_by_user_by_day'] = max_amount_by_user_by_day
                # cash_activity_data['max_amount_by_user'] = max_amount_by_user
                # cash_activity_data['max_amount_by_customeruser'] = max_amount_by_customeruser
                cash_activity = CashActivity.objects.create(**cash_activity_data)
                activity_id = f'SUT{str(cash_activity.id).zfill(8)}'
                cash_activity.activity_id = activity_id
                cash_activity.save()
                if str(activity_type) in ['1', '4', '8']:
                    if not all([max_distributed_amount, min_distributed_amount]):
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '自定义面额活动须上传派发区间值'}, status=status.HTTP_400_BAD_REQUEST)
                    try:
                        min_distributed_amount = Decimal(min_distributed_amount)
                        max_distributed_amount = Decimal(max_distributed_amount)
                    except:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '区间值格式有误'}, status=status.HTTP_400_BAD_REQUEST)
                    if max_distributed_amount < min_distributed_amount:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '区间值格式有误'}, status=status.HTTP_400_BAD_REQUEST)
                    if min_distributed_amount < 1 or max_distributed_amount > 10000:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '区间值格式有误'}, status=status.HTTP_400_BAD_REQUEST)
                    cash_activity.max_distributed_amount = max_distributed_amount
                    cash_activity.min_distributed_amount = min_distributed_amount
                    cash_activity.save()
                elif str(activity_type) in ['2', '5']:
                    if not cash_activity_rule:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '缺少规则参数'}, status=status.HTTP_400_BAD_REQUEST)
                    # probability = Decimal(0)
                    for rule in cash_activity_rule:
                        # if probability > Decimal(max_amount):
                        #     transaction.savepoint_rollback(save_id)
                        #     return Response({'error': '配置规则超过最大派发金额'}, status=status.HTTP_400_BAD_REQUEST)
                        CashActivityRule.objects.create(amount=rule['amount'], probability=rule['probability'], cash_activity_id=cash_activity.id)
                        # probability += (Decimal(rule['amount']) * Decimal(rule['probability']).quantize(Decimal("0")))
                else:
                    if not cash_activity_rule:
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': '缺少规则参数'}, status=status.HTTP_400_BAD_REQUEST)
                    probability = 0
                    for rule in cash_activity_rule:
                        if probability > 100:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '规则比例超过100%'}, status=status.HTTP_400_BAD_REQUEST)
                        CashActivityRule.objects.create(amount=rule['amount'], probability=rule['probability'], cash_activity_id=cash_activity.id)
                        probability += int(Decimal(rule['probability']).quantize(Decimal("0")))
                cashactivitylevelrecord_data = {}
                cashactivitylevelrecord_data['amount'] = max_amount
                cashactivitylevelrecord_data['operation_type'] = 7
                cashactivitylevelrecord_data['activity_type'] = 2
                cashactivitylevelrecord_data['remain_amount'] = max_amount
                cashactivitylevelrecord_data['p_remain_amount'] = max_amount
                cashactivitylevelrecord_data['max_amount'] = max_amount
                cashactivitylevelrecord_data['p_max_amount'] = max_amount
                cashactivitylevelrecord_data['user_id'] = user.id
                cashactivitylevelrecord_data['remark'] = '超管新增活动'
                cashactivitylevelrecord_data['activity_id'] = cash_activity.id
                CashActivityLevelRecord.objects.create(**cashactivitylevelrecord_data)
                transaction.savepoint_commit(save_id)
                try:
                    conn = pymysql.connect(host=settings.DATABASES['fnonline']['HOST'],
                                           port=settings.DATABASES['fnonline']['PORT'],
                                           database=settings.DATABASES['fnonline']['NAME'],
                                           user=settings.DATABASES['fnonline']['USER'],
                                           password=settings.DATABASES['fnonline']['PASSWORD'])
                    cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
                    if str(ali_channel_id) == '1':
                        ali_platform_id = 15
                    elif str(ali_channel_id) == '8':
                        ali_platform_id = 49
                    else:
                        ali_platform_id = 66
                    sql = f'''
                    INSERT INTO z_platform_activity(activity_name,activity_type,platform_type,activity_status,template_id,publish_start_time,publish_end_time,total_amount,expend_amount,settle_amount,is_bind,wx_platform_id,ali_platform_id,is_self,disposition_amount) VALUES("{name}",2,3,1,"{activity_id}","{available_begin_time}","{available_end_time}","{max_amount}","0","0",0,16,{ali_platform_id},0,"{max_amount}");
                    '''
                    cursor.execute(sql)
                    conn.commit()
                finally:
                    cursor.close()
                    conn.close()
                return Response({'success': '创建成功', 'id': cash_activity.id}, status=status.HTTP_200_OK)
            except Exception as e:
                transaction.savepoint_rollback(save_id)
                return Response({'error': '创建失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        amount = self.request.data.get('amount', None)
        instance = self.get_object()
        if not amount:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            amount = Decimal(amount)
        except:
            return Response({'error': '请上传数值参数'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_state == 4:
            return Response({'error': '已结束活动不可操作'}, status=status.HTTP_400_BAD_REQUEST)
        if amount < Decimal(0):
            max_amount__sum = instance.cashactivitylevel_set.all().aggregate(Sum('max_amount'))['max_amount__sum']
            if max_amount__sum:
                remain_amount = instance.max_amount - max_amount__sum
            else:
                remain_amount = instance.max_amount
            if -amount > remain_amount:
                return Response({'error': '活动剩余额度不足扣减'}, status=status.HTTP_400_BAD_REQUEST)
            change_amount = -amount
            operation_type = 8
            remark = '超管操作减少预算'
        else:
            change_amount = amount
            operation_type = 7
            remark = '超管操作增加预算'
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            try:
                instance_id = instance.id
                while True:
                    activity = CashActivity.objects.get(id=instance_id)
                    activity_origin_remain_amount = activity.remain_amount
                    activity_origin_max_amount = activity.max_amount
                    activity_new_remain_amount = activity_origin_remain_amount + amount
                    activity_new_max_amount = activity_origin_max_amount + amount
                    result = CashActivity.objects.filter(remain_amount=activity_origin_remain_amount, id=instance_id).update(max_amount=activity_new_max_amount, remain_amount=activity_new_remain_amount)
                    if result == 0:
                        continue
                    break
                cashactivitylevelrecord_data = {}
                cashactivitylevelrecord_data['amount'] = change_amount
                cashactivitylevelrecord_data['operation_type'] = operation_type
                cashactivitylevelrecord_data['activity_type'] = 2
                cashactivitylevelrecord_data['remain_amount'] = activity_new_remain_amount
                cashactivitylevelrecord_data['p_remain_amount'] = activity_new_remain_amount
                cashactivitylevelrecord_data['max_amount'] = activity_new_max_amount
                cashactivitylevelrecord_data['p_max_amount'] = activity_new_max_amount
                cashactivitylevelrecord_data['user_id'] = user.id
                cashactivitylevelrecord_data['remark'] = remark
                cashactivitylevelrecord_data['activity_id'] = instance_id
                CashActivityLevelRecord.objects.create(**cashactivitylevelrecord_data)
            except Exception as e:
                logging.error(e)
                transaction.savepoint_rollback(save_id)
                return Response({'error': '修改失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        transaction.savepoint_commit(save_id)
        return Response({'success': '修改成功'}, status=status.HTTP_200_OK)

    def cashactivity_bind_developer(self, request):
        user = self.request.iser
        activity_id = self.request.data.get('activity_id', None)
        developer_id = self.request.data.get('developer_id', None)
        try:
            instance = self.get_queryset().get(id=activity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        if instance.activity_type != 8:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            developer = User.objects.get(id=developer_id)
        except:
            return Response({'error': '无效开发者id'}, status=status.HTTP_400_BAD_REQUEST)
        if developer.is_developer != 1:
            return Response({'error': '关联用户尚未成为开发者'}, status=status.HTTP_400_BAD_REQUEST)
        if developer.is_active != 1:
            return Response({'error': '关联用户状态有误'}, status=status.HTTP_400_BAD_REQUEST)
        data = {}
        data['developer_id'] = developer_id
        data['cashactivity_id'] = activity_id
        data['create_user_id'] = user.id
        CashActivityAPIRelation.objects.get_or_create(defaults=data, developer_id=developer_id, cashactivity_id=activity_id)
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def cashactivity_unbind_developer(self, request):
        id = self.request.data.get('id', None)
        if not id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            CashActivityAPIRelation.objects.filter(id=id).delete()
            return Response({'error': '操作成功'}, status=status.HTTP_200_OK)
        except:
            return Response({'error': '操作失败'}, status=status.HTTP_400_BAD_REQUEST)

    def update_cashactivity_white_table(self, request):
        user = self.request.iser
        add_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cashactivity_id = self.request.data.get('cashactivity_id', None)
        mobile_array = self.request.data.get('mobile_array', None)
        try:
            instance = self.get_queryset().get(id=cashactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            mobile_array = list(set(mobile_array))
        except:
            return Response({'error': '手机号码参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        insert_sql = 'INSERT INTO tb_cash_activity_white_table(cashactivity_id,mobile,is_admin,add_user_id, level_id,add_time) VALUES '
        for mobile in mobile_array:
            if not re.match(r'^1[3-9]\d{9}$', mobile):
                return Response({'error': f'{mobile}非手机号格式'}, status=status.HTTP_400_BAD_REQUEST)
            insert_sql += f'({cashactivity_id},"{mobile}",1,{user.id},{user.level_id},"{add_time}"),'
        insert_sql = insert_sql[:-1]
        insert_sql += ';'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            delete_sql = f'DELETE FROM tb_cash_activity_white_table WHERE cashactivity_id={cashactivity_id} AND is_admin=1;'
            cursor.execute(delete_sql)
            conn.commit()
            if mobile_array:
                cursor.execute(insert_sql)
                conn.commit()
        except Exception as e:
            conn.rollback()
            cursor.close()
            conn.close()
            return Response({'error': '修改失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        cursor.close()
        conn.close()
        return Response({'success': '修改成功'}, status=status.HTTP_200_OK)

    def clear_cashactivity_white_table(self, request):
        user = self.request.iser
        cashactivity_id = self.request.data.get('cashactivity_id', None)
        try:
            instance = self.get_queryset().get(id=cashactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            delete_sql = f'DELETE FROM tb_cash_activity_white_table WHERE cashactivity_id={cashactivity_id} AND is_admin=1;'
            cursor.execute(delete_sql)
            conn.commit()
        except Exception as e:
            conn.rollback()
            cursor.close()
            conn.close()
            return Response({'error': '清空失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
        cursor.close()
        conn.close()
        return Response({'success': '清空成功'}, status=status.HTTP_200_OK)

    def add_cashactivity_white_table(self, request):
        user = self.request.iser
        add_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        cashactivity_id = self.request.data.get('cashactivity_id', None)
        my_file = self.request.FILES.get('my_file', None)
        try:
            instance = self.get_queryset().get(id=cashactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        # business_no_list = CashActivityWhiteTable.objects.filter(cashactivity_id=cashactivity_id).annotate(merged_field=Concat(F('mobile'),Value(','),F('business_no'),output_field=CharField())).values_list('merged_field', flat=True)
        business_no_list = CashActivityWhiteTable.objects.filter(cashactivity_id=cashactivity_id).values_list('mobile', flat=True)
        table = wb.sheets()[0]
        nrows = table.nrows
        if nrows > 1000:
            return Response({'error': '文件数量超过1000,请拆分多份上传'}, status=status.HTTP_400_BAD_REQUEST)
        title = table.row_values(0)
        if instance.is_white_merchant == 1:
            if '商户号' not in title:
                return Response({'error': '表格字段缺少"商户号"'}, status=status.HTTP_400_BAD_REQUEST)
            business_no_index = title.index('商户号')
        if '联系人手机号' not in title:
            return Response({'error': '表格字段缺少"联系人手机号"'}, status=status.HTTP_400_BAD_REQUEST)
        mobile_index = title.index('联系人手机号')
        insert_sql = 'INSERT INTO tb_cash_activity_white_table(cashactivity_id,mobile,is_admin,add_user_id, level_id,add_time,business_no) VALUES '
        a = 0
        for i in range(1, nrows):
            rowValues = table.row_values(i)
            mobile = str(rowValues[mobile_index]).replace('.0', '').replace(' ', '')
            if instance.is_white_merchant == 1:
                business_no = str(rowValues[business_no_index]).replace('.0', '').replace(' ', '')
            else:
                business_no = ''
            if mobile not in business_no_list:
                insert_sql += f'({cashactivity_id},"{mobile}",1,{user.id},{user.level_id},"{add_time}","{business_no}"),'
                a = 1
        insert_sql = insert_sql[:-1]
        insert_sql += ';'
        if a:
            conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                                   port=settings.DATABASES['default']['PORT'],
                                   database=settings.DATABASES['default']['NAME'],
                                   user=settings.DATABASES['default']['USER'],
                                   password=settings.DATABASES['default']['PASSWORD'])
            cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
            try:
                cursor.execute(insert_sql)
                conn.commit()
            except Exception as e:
                conn.rollback()
                cursor.close()
                conn.close()
                return Response({'error': '添加失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
            cursor.close()
            conn.close()
        return Response({'success': '添加成功'}, status=status.HTTP_200_OK)
        # file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'

    def cashactivity_white_table(self, request):
        cashactivity_id = self.request.data.get('cashactivity_id', None)
        try:
            instance = self.get_queryset().get(id=cashactivity_id)
        except:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        cashactivitywhitetable_set = instance.cashactivitywhitetable_set.filter(is_admin=1).values_list('mobile', flat=True)
        return Response({'data': list(cashactivitywhitetable_set)}, status=status.HTTP_200_OK)


class CashActivityWhiteTableModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('mobile', 'business_no')
    filter_class = CashActivityWhiteTableFilter
    serializer_class = CashActivityWhiteTableModelSerializer

    def get_queryset(self):
        queryset = CashActivityWhiteTable.objects.filter()
        return queryset.order_by('-id')

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        instance.delete()
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def export_cashactivitywhitetable(self, request):
        user = self.request.iser
        cashactivity_id = self.request.query_params.get('cashactivity_id', None)
        level_id = self.request.query_params.get('level_id', None)
        is_admin = self.request.query_params.get('is_admin', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (mobile LIKE "%{search}%" OR business_no LIKE "%{search}%")'
        if cashactivity_id:
            condition = condition + f' AND cashactivity_id in ({cashactivity_id})'
        if is_admin:
            condition = condition + f' AND is_admin in ({is_admin})'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''SELECT e.*,f.nickname FROM (SELECT c.*, d.city_name,district_name,branch_name FROM (SELECT * FROM (SELECT id,mobile,is_admin,add_user_id,DATE_FORMAT(add_time,'%Y-%m-%d %H:%i:%S')AS add_time,business_no FROM tb_cash_activity_white_table {condition})AS a LEFT JOIN (SELECT wyfMerchantID,`name`AS merchant_name,managerName,managermobile,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,nickname FROM tb_u_user)AS f ON e.add_user_id=f.id;'''
            cursor.execute(sql)
        except:
            sql = f'''SELECT e.*,f.nickname FROM (SELECT c.*, d.city_name,district_name,branch_name FROM (SELECT * FROM (SELECT id,mobile,is_admin,add_user_id,DATE_FORMAT(add_time,'%Y-%m-%d %H:%i:%S')AS add_time,business_no FROM tb_cash_activity_white_table)AS a LEFT JOIN (SELECT wyfMerchantID,`name`AS merchant_name,managerName,managermobile,level_id FROM tb_merchant)AS b ON a.business_no=b.wyfMerchantID)AS c LEFT JOIN (SELECT id,city_name,district_name,branch_name FROM tb_u_level)AS d ON c.level_id=d.id)AS e LEFT JOIN (SELECT id,nickname FROM tb_u_user)AS f ON e.add_user_id=f.id;'''
            cursor.execute(sql)
        ziduan = ['ID', '创建人角色', '创建人', '创建时间', '白名单商户号', '白名单手机号', '商户名称', '负责人姓名', '负责人手机号', '所属市公司', '所属区县', '所属网点']
        cols = ['id', 'is_admin', 'nickname', 'add_time', 'business_no', 'mobile', 'merchant_name', 'managerName', 'managermobile', 'city_name', 'district_name', 'branch_name']
        response = StreamingHttpResponse(self.get_cashactivitywhitetable_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('微邮惠礼包白名单.csv'))
        return response

    def get_cashactivitywhitetable_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_ADMIN_ENUM = {
            1: '超级管理员',
            0: '客户经理'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_admin':
                    tmp_str += '"%s",' % (IS_ADMIN_ENUM[row[col]])
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class CashActivityLevelModelViewSet(ModelViewSet):

    pagination_class = MyPage
    search_fields = ('level__name', )
    filter_class = CashActivityLevelFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        sort = self.request.query_params.get('sort', '')
        # activity_id = self.request.query_params.get('activity_id', None)
        # if activity_id:
        #     try:
        #         activity = AliDiscountCoupon.objects.get(id=activity_id)
        #         max_number__sum = activity.alidiscountcouponlevel_set.all().aggregate(Sum('max_number'))['max_number__sum']
        #         if max_number__sum:
        #             remain_number = activity.max_number - max_number__sum
        #         else:
        #             remain_number = activity.max_number
        #         if '_mutable' in self.request.data.__dict__:
        #             self.request.data.__dict__['_mutable'] = True
        #         self.request.data['can_remain_number'] = str(remain_number)
        #     except:
        #         pass
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = CashActivityLevel.objects.filter()
        elif level.level == 1:
            queryset = CashActivityLevel.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = CashActivityLevel.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = CashActivityLevel.objects.filter(level__district_id=level.id)
        else:
            queryset = CashActivityLevel.objects.filter(level__branch_id=level.id)
        if sort == '0':
            queryset = queryset.order_by('p_max_amount', 'id')
        elif sort == '1':
            queryset = queryset.order_by('-p_max_amount', 'id')
        elif sort == '2':
            queryset = queryset.order_by('p_take_amount', 'id')
        elif sort == '3':
            queryset = queryset.order_by('-p_take_amount', 'id')
        elif sort == '4':
            queryset = queryset.order_by('p_remain_amount', 'id')
        elif sort == '5':
            queryset = queryset.order_by('-p_remain_amount', 'id')
        elif sort == '6':
            queryset = queryset.order_by(F('p_take_amount') - F('p_receive_amount'), 'id')
        elif sort == '7':
            queryset = queryset.order_by(-(F('p_take_amount') - F('p_receive_amount')), 'id')
        elif sort == '8':
            queryset = queryset.order_by('p_receive_amount', 'id')
        elif sort == '9':
            queryset = queryset.order_by('-p_receive_amount', 'id')
        else:
            queryset = queryset.order_by('level__level', 'id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CashActivityLevelListModelSerializer
        else:
            return CashActivityLevelRetrieveModelSerializer

    def export_cashactivitylevel(self, obj):
        activity_id = self.request.query_params.get('activity_id', None)
        search = self.request.query_params.get('search', None)
        dimensionality = self.request.query_params.get('dimensionality', '0,1,2,3,4')
        user = self.request.iser
        if not search:
            if user.level.level == 0:
                condition = f'WHERE level IN ({dimensionality})'
            elif user.level.level == 1:
                condition = f'WHERE prov_id={user.level_id} AND level IN ({dimensionality})'
            elif user.level.level == 2:
                condition = f'WHERE city_id={user.level_id} AND level IN ({dimensionality})'
            elif user.level.level == 3:
                condition = f'WHERE district_id={user.level_id} AND level IN ({dimensionality})'
            else:
                condition = f'WHERE branch_id={user.level_id} AND level IN ({dimensionality})'
        else:
            if user.level.level == 0:
                condition = f'WHERE name LIKE "%{search}%" AND level IN ({dimensionality})'
            elif user.level.level == 1:
                condition = f'WHERE prov_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
            elif user.level.level == 2:
                condition = f'WHERE city_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
            elif user.level.level == 3:
                condition = f'WHERE district_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
            else:
                condition = f'WHERE branch_id={user.level_id} AND name LIKE "%{search}%" AND level IN ({dimensionality})'
        if not activity_id:
            condition1 = 'WHERE'
        else:
            condition1 = f'WHERE cash_activity_id="{activity_id}" AND'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        sql = f'''
            SELECT c.*,d.name,account,prov_name,city_name,district_name,branch_name,`level` FROM (SELECT a.*,b.activity_name FROM (SELECT id,level_id,cash_activity_id,p_max_amount,p_take_amount,p_remain_amount,(p_take_amount-p_receive_amount)AS p_unclaimed_amount,p_receive_amount,remain_amount,max_amount,receive_amount,take_amount,(take_amount-receive_amount)AS unclaimed_amount FROM tb_cash_activity_level {condition1} level_id IN (SELECT id FROM tb_u_level {condition}))AS a LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS b ON a.cash_activity_id=b.id)AS c LEFT JOIN (SELECT id,name,account,prov_name,city_name,district_name,branch_name,`level` FROM tb_u_level)AS d ON c.level_id=d.id ORDER BY `level`;
            '''
        cursor.execute(sql)
        ziduan = ['id', '活动名称', '机构名称', '机构号', '省', '市', '区县', '网点', '活动预算', '已派发额度', '剩余预算', '待领取', '已领取', '机构分得预算', '机构已派发额度', '机构剩余可派发额度', '机构待领取额度', '机构已领取额度']
        cols = ['id', 'activity_name', 'name', 'account', 'prov_name', 'city_name', 'district_name', 'branch_name', 'p_max_amount', 'p_take_amount', 'p_remain_amount', 'p_unclaimed_amount', 'p_receive_amount', 'max_amount', 'take_amount', 'remain_amount', 'unclaimed_amount', 'receive_amount']
        response = StreamingHttpResponse(self.get_cashactivitylevel_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('微信礼包机构分配明细.csv'))
        return response

    def get_cashactivitylevel_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                # if col == 'unclaimed_number':
                #     tmp_str += '"%s",' % (row['take_number']-row['receive_number'])
                # elif col == 'wait_number':
                #     tmp_str += '"%s",' % (row['receive_number']-row['use_number'])
                if col in ['p_max_amount', 'p_take_amount', 'p_remain_amount', 'p_unclaimed_amount', 'p_receive_amount', 'max_amount', 'take_amount', 'remain_amount', 'unclaimed_amount', 'receive_amount']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else 0)
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def batch_update_max_amount(self, request):
        data_array = self.request.data.get('data_array', None)
        if not data_array:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not isinstance(data_array, list):
            return Response({'error': '参数格式有误'}, status=status.HTTP_400_BAD_REQUEST)
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()

            # 暴力回滚
            try:
                for data in data_array:
                    instance = CashActivityLevel.objects.get(id=data['id'])
                    amount = -Decimal(data['amount'])
                    if amount < 0 and instance.remain_amount < abs(amount):
                        transaction.savepoint_rollback(save_id)
                        return Response({'error': f'{instance.id}剩余数量不足扣减'}, status=status.HTTP_400_BAD_REQUEST)
                    cash_activity = instance.cash_activity
                    instance.max_amount = instance.max_amount + amount
                    instance.remain_amount = instance.remain_amount + amount
                    instance.p_max_amount = instance.p_max_amount + amount
                    instance.p_remain_amount = instance.p_remain_amount + amount
                    instance.save()
                    cash_activity.max_amount += amount
                    cash_activity.remain_amount += amount
                    cash_activity.save()
                    relation_set = instance.child_set.all()
                    for relation in relation_set:
                        parent = relation.parent
                        parent.p_max_amount += amount
                        parent.p_remain_amount += amount
                        parent.save()
            except Exception as e:
                logger.error(e)
                # 事务回滚
                transaction.savepoint_rollback(save_id)
                return Response({'error': '修改失败'}, status=status.HTTP_400_BAD_REQUEST)
        transaction.savepoint_commit(save_id)
        return Response({'success': '操作成功'}, status=status.HTTP_200_OK)

    def update(self, request, *args, **kwargs):
        user = self.request.iser
        instance = self.get_object()
        max_amount = self.request.data.get('max_amount', None)
        try:
            max_amount = Decimal(max_amount)
        except:
            return Response({'error': '请输入整数额度'}, status=status.HTTP_400_BAD_REQUEST)
        cash_activity = instance.cash_activity
        if cash_activity.activity_type not in [1, 2, 3]:
            return Response({'error': '共享额度活动不可调整额度'}, status=status.HTTP_400_BAD_REQUEST)
        if cash_activity.activity_state == 4:
            return Response({'error': '已结束活动不可操作'}, status=status.HTTP_400_BAD_REQUEST)
        # if instance.max_amount <= 0:
        #     return Response({'error': '机构活动额度不足'}, status=status.HTTP_400_BAD_REQUEST)
        instance_id = instance.id
        with transaction.atomic():
            # 创建事务保存点
            save_id = transaction.savepoint()
            try:
                while True:
                    instance = CashActivityLevel.objects.get(id=instance_id)
                    amount = max_amount - instance.max_amount
                    if amount > 0:
                        max_amount__sum = cash_activity.cashactivitylevel_set.all().aggregate(Sum('max_amount'))['max_amount__sum']
                        if max_amount__sum:
                            remain_amount = cash_activity.max_amount - max_amount__sum
                        else:
                            remain_amount = cash_activity.max_amount
                        if remain_amount < amount:
                            transaction.savepoint_rollback(save_id)
                            return Response({'error': '活动额度不足'}, status=status.HTTP_400_BAD_REQUEST)
                        operation_type = 1
                        remark = '超管通过后台分配增加'
                        amount1 = amount
                    else:
                        if instance.remain_amount < -amount:
                            return Response({'error': '机构活动额度不足'}, status=status.HTTP_400_BAD_REQUEST)
                        operation_type = 2
                        remark = '超管通过后台分配减少'
                        amount1 = -amount
                    origin_remain_amount = instance.remain_amount
                    origin_max_amount = instance.max_amount
                    origin_p_remain_amount = instance.p_remain_amount
                    origin_p_max_amount = instance.p_max_amount
                    new_remain_amount = origin_remain_amount + amount
                    new_max_amount = origin_max_amount + amount
                    new_p_remain_amount = origin_p_remain_amount + amount
                    new_p_max_amount = origin_p_max_amount + amount
                    if new_max_amount == Decimal(0):
                        is_auth = 0
                    else:
                        is_auth = 1
                    result = CashActivityLevel.objects.filter(id=instance_id, remain_amount=origin_remain_amount).update(
                        max_amount=new_max_amount,
                        remain_amount=new_remain_amount,
                        p_max_amount=new_p_max_amount,
                        p_remain_amount=new_p_remain_amount,
                        is_auth=is_auth
                    )
                    if result == 0:
                        continue
                    try:
                        cashactivitylevelrecord_data = {}
                        cashactivitylevelrecord_data['activity_level_id'] = instance.id
                        cashactivitylevelrecord_data['amount'] = amount1
                        cashactivitylevelrecord_data['operation_type'] = operation_type
                        cashactivitylevelrecord_data['remain_amount'] = new_remain_amount
                        cashactivitylevelrecord_data['p_remain_amount'] = new_p_remain_amount
                        cashactivitylevelrecord_data['max_amount'] = new_max_amount
                        cashactivitylevelrecord_data['p_max_amount'] = new_p_max_amount
                        cashactivitylevelrecord_data['user_id'] = user.id
                        cashactivitylevelrecord_data['remark'] = remark
                        CashActivityLevelRecord.objects.create(**cashactivitylevelrecord_data)
                    except Exception as e:
                        print(e)
                        pass
                    break
                relation_set = instance.child_set.all()
                for relation in relation_set:
                    parent = relation.parent
                    parent.p_max_amount += amount
                    parent.p_remain_amount += amount
                    parent.save()
            except Exception as e:
                logger.error(e)
                transaction.savepoint_rollback(save_id)
                return Response({'error': '调配失败,请联系管理员'}, status=status.HTTP_400_BAD_REQUEST)
            transaction.savepoint_commit(save_id)
            return Response({'success': '调配成功'}, status=status.HTTP_200_OK)

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        if instance.p_take_amount != 0:
            return Response({'error': '已派发额度,不可删除'}, status=status.HTTP_400_BAD_REQUEST)
        cash_activity = instance.cash_activity
        if cash_activity.activity_type in [1, 2, 3]:
            relation_set = instance.child_set.all()
            for relation in relation_set:
                parent = relation.parent
                parent.p_max_amount -= instance.max_amount
                parent.p_remain_amount -= instance.p_remain_amount
                parent.save()
                if parent.p_max_amount == Decimal(0):
                    parent.delete()
            relation_set = instance.parent_set.all()
            for relation in relation_set:
                relation.delete()
                child = relation.child
                child.delete()
        instance.delete()
        return Response({'success': '撤销成功'}, status=status.HTTP_200_OK)

    def cash_distributed_statistical(self, request):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        if not cash_activity_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        instance = self.get_queryset().filter(cash_activity_id=cash_activity_id).order_by('level__level').first()
        if not instance:
            return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        serializer = CashActivityLevelStatisticalModelSerializer(instance)
        return Response({"statisticsData": serializer.data, 'date_now': datetime.datetime.now().strftime('%Y年%m月%d日')}, status=status.HTTP_200_OK)

    def cashactivitylevel_tree(self, requset):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        if not cash_activity_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        instance = self.get_queryset().filter(cash_activity_id=cash_activity_id).order_by('level__level').first()
        if not instance:
            return Response({}, status=status.HTTP_200_OK)
        serializer = CashActivityLevelTreeModelSerializer(instance)
        return Response(serializer.data, status=status.HTTP_200_OK)

    def cashactivitylevel_tree1(self, requset):
        cash_activity_id = self.request.query_params.get('cash_activity_id', None)
        cash_activity_level_id = self.request.query_params.get('cash_activity_level_id', None)
        if not cash_activity_id:
            return Response({'error': '缺少必传参数'}, status=status.HTTP_400_BAD_REQUEST)
        if not cash_activity_level_id:
            queryset = self.get_queryset().filter(cash_activity_id=cash_activity_id).order_by('level__level')[:1]
        else:
            try:
                instance = self.get_queryset().get(id=cash_activity_level_id)
            except:
                return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
            level = instance.level
            if level.level == 1:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=2)
            elif level.level == 2:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=3)
            elif level.level == 3:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=4)
            elif level.level == 0:
                queryset = self.get_queryset().filter(child_set__parent_id=instance.id, level__level=1)
            else:
                queryset = self.get_queryset().filter(level__level=5)
        # if not instance:
        #     return Response({'error': '无效活动id'}, status=status.HTTP_400_BAD_REQUEST)
        serializer = CashActivityLevelListModelSerializer(queryset, many=True)
        return Response(serializer.data, status=status.HTTP_200_OK)


class CashActivityDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = CashActivityDataFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('customer_user__mobile', 'mobile')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level', None)
        cash_activity_level_id = self.request.query_params.get('cash_activity_level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        try:
            if '_mutable' in self.request.data.__dict__:
                self.request.data.__dict__['_mutable'] = True
        except:
            pass
        if level.level == 0:
            queryset = CashActivityData.objects.all().order_by('-id')
        elif level.level == 1:
            queryset = CashActivityData.objects.filter(level__prov_id=level.id).order_by('-id')
        elif level.level == 2:
            queryset = CashActivityData.objects.filter(level__city_id=level.id).order_by('-id')
        elif level.level == 3:
            queryset = CashActivityData.objects.filter(level__district_id=level.id).order_by('-id')
        else:
            queryset = CashActivityData.objects.filter(level__branch_id=level.id).order_by('-id')
        if cash_activity_level_id:
            try:
                activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
                self.request.data['acitivty_info'] = CashActivityLevelListModelSerializer(activity_level).data
                relation_set = list(activity_level.parent_set.all().values_list('child_id', flat=True))
                relation_set.append(cash_activity_level_id)
                queryset = queryset.filter(cash_activity_level_id__in=relation_set)
            except:
                self.request.data['acitivty_info'] = {}
            # try:
            #     cash_activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
            #     queryset = queryset.filter(cash_activity_id=cash_activity_level.cash_activity_id)
            # except:
            #     queryset = queryset.filter(cash_activity_level_id=cash_activity_level_id)
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return CashActivityDataListModelSerializer
        else:
            return CashActivityDataRetrieveModelSerializer

    def export_cashactivitydata(self, request):
        user = self.request.iser
        activity_id = self.request.query_params.get('activity_id', None)
        cash_activity_level_id = self.request.query_params.get('cash_activity_level_id', None)
        level_id = self.request.query_params.get('level_id', None)
        state = self.request.query_params.get('state', None)
        search = self.request.query_params.get('search', None)
        create_time_begin = self.request.query_params.get('create_time_begin', None)
        create_time_end = self.request.query_params.get('create_time_end', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND customer_user_id IN (SELECT id FROM tb_u_customer_user WHERE mobile LIKE "%{search}%")'
        if activity_id:
            condition = condition + f' AND cash_activity_id in ({activity_id})'
        if state:
            condition = condition + f' AND state in ({state})'
        if create_time_begin:
            condition = condition + f' AND create_time >= "{create_time_begin + " 00:00:00"}"'
        if create_time_end:
            condition = condition + f' AND create_time <= "{create_time_end + " 23:59:59"}"'
        if cash_activity_level_id:
            activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
            relation_set = list(activity_level.parent_set.all().values_list('child_id', flat=True))
            relation_set.append(cash_activity_level_id)
            if len(relation_set) == 1:
                condition = condition + f' AND cash_activity_level_id IN {str(tuple(relation_set)).replace(",", "")}'
            else:
                condition = condition + f' AND cash_activity_level_id IN {tuple(relation_set)}'

        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            cashactivity = CashActivity.objects.get(id=activity_id)
        except:
            sql = f'''
            SELECT k.*,l.mchid,mchid_name FROM (SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name,account_nine FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mobile,channel_id FROM tb_cash_activity_data WHERE id=1)AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name,account_nine FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id)AS k LEFT JOIN (SELECT id,mchid,name AS mchid_name FROM tb_channel)AS l ON k.channel_id=l.id;
            '''
            cursor.execute(sql)
            ziduan = ['ID', '活动名称', '活动id', '所属市公司', '所属区县', '所属网点', '领取金额', '领取状态', '派发时间', '领取时间', '撤销时间', '用户openid',
                      '领取人姓名', '领取人手机号',
                      '领取人身份证', '派发员工', '派发员工手机号', '派发员工所属网点', '派发员工网点机构号', '领取渠道', '派发服务商号', '派发流水号', '失败原因', '机构号']
            cols = ['id', 'activity_name', 'cash_activity_id', 'city_name', 'district_name', 'branch_name', 'amount',
                    'state', 'create_time', 'received_time', 'expired_time', 'openid', 'realname', 'mobile', 'idcode',
                    'companyuser_name', 'companyuser_mobile', 'companyuser_banch', 'companyuser_account', 'mchid_name',
                    'mchid', 'payment_no', 'return_msg', 'account_nine']
            response = StreamingHttpResponse(self.get_cashactivitydata_fromat(cols, cursor, conn, ziduan))
            response['Content-Type'] = 'application/octet-stream; charset=gbk'
            response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('微邮惠礼包派发数据.csv'))
            return response
        if cashactivity.is_merchant == 1:
            try:
                sql = f'''
                SELECT q.*,r.merchant_level_name FROM (SELECT * FROM (SELECT * FROM (SELECT k.*,l.mchid,mchid_name FROM (SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name,account_nine FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mobile,channel_id FROM tb_cash_activity_data {condition})AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name,account_nine FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id)AS k LEFT JOIN (SELECT id,mchid,name AS mchid_name FROM tb_channel)AS l ON k.channel_id=l.id)AS m LEFT JOIN (SELECT activitydata_id,business_no,is_white_table FROM tb_cash_activity_data_white_table)AS n ON m.id=n.activitydata_id)AS o LEFT JOIN (SELECT wyfMerchantID,`name` AS merchant_name,level_id AS merchant_level_id FROM tb_merchant)AS p ON o.business_no=p.wyfMerchantID)AS q LEFT JOIN (SELECT id,`name`AS merchant_level_name FROM tb_u_level)AS r ON q.merchant_level_id=r.id;
                '''
                cursor.execute(sql)
            except:
                sql = f'''
                SELECT q.*,r.merchant_level_name FROM (SELECT * FROM (SELECT * FROM (SELECT k.*,l.mchid,mchid_name FROM (SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name,account_nine FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mobile,channel_id FROM tb_cash_activity_data WHERE id=1)AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name,account_nine FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id)AS k LEFT JOIN (SELECT id,mchid,name AS mchid_name FROM tb_channel)AS l ON k.channel_id=l.id)AS m LEFT JOIN (SELECT activitydata_id,business_no,is_white_table FROM tb_cash_activity_data_white_table)AS n ON m.id=n.activitydata_id)AS o LEFT JOIN (SELECT wyfMerchantID,`name` AS merchant_name,level_id AS merchant_level_id FROM tb_merchant)AS p ON o.business_no=p.wyfMerchantID)AS q LEFT JOIN (SELECT id,`name`AS merchant_level_name FROM tb_u_level)AS r ON q.merchant_level_id=r.id;
                '''
                cursor.execute(sql)
            ziduan = ['ID', '活动名称', '活动id', '所属市公司', '所属区县', '所属网点', '领取金额', '领取状态', '派发时间', '领取时间', '撤销时间', '用户openid',
                      '领取人姓名', '领取人手机号',
                      '领取人身份证', '派发员工', '派发员工手机号', '派发员工所属网点', '派发员工网点机构号', '领取渠道', '派发服务商号', '派发流水号', '失败原因', '机构号',
                      '微邮付商户号', '商户名称', '商户归属', '是否通过白名单参与']
            cols = ['id', 'activity_name', 'cash_activity_id', 'city_name', 'district_name', 'branch_name', 'amount',
                    'state', 'create_time', 'received_time', 'expired_time', 'openid', 'realname', 'mobile', 'idcode',
                    'companyuser_name', 'companyuser_mobile', 'companyuser_banch', 'companyuser_account', 'mchid_name',
                    'mchid', 'payment_no', 'return_msg', 'account_nine', 'business_no', 'merchant_name', 'merchant_level_name', 'is_white_table']
            response = StreamingHttpResponse(self.get_cashactivitydata_fromat(cols, cursor, conn, ziduan))
            response['Content-Type'] = 'application/octet-stream; charset=gbk'
            response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('微邮惠礼包派发数据.csv'))
            return response
        else:
            try:
                sql = f'''
                SELECT k.*,l.mchid,mchid_name FROM (SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name,account_nine FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mobile,channel_id FROM tb_cash_activity_data {condition})AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name,account_nine FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id)AS k LEFT JOIN (SELECT id,mchid,name AS mchid_name FROM tb_channel)AS l ON k.channel_id=l.id;
                '''
                cursor.execute(sql)
            except:
                sql = f'''
                SELECT k.*,l.mchid,mchid_name FROM (SELECT i.*,j.activity_name FROM (SELECT g.*, h.account,branch_name,city_name,district_name,account_nine FROM (SELECT e.*, f.companyuser_account,companyuser_banch FROM (SELECT c.*,d.idcode,realname,openid FROM (SELECT a.*,b.companyuser_level_id,companyuser_mobile,companyuser_name FROM (SELECT id,cash_activity_id,amount,state,DATE_FORMAT(create_time,'%Y-%m-%d %H:%i:%S')AS create_time,DATE_FORMAT(received_time,'%Y-%m-%d %H:%i:%S')AS received_time,DATE_FORMAT(expired_time,'%Y-%m-%d %H:%i:%S')AS expired_time,customer_user_id,companyuser_id,payment_no,return_msg,level_id,mobile,channel_id FROM tb_cash_activity_data WHERE id=1)AS a LEFT JOIN (SELECT id,nickname AS companyuser_name,mobile AS companyuser_mobile,level_id AS companyuser_level_id FROM tb_u_user)AS b ON a.companyuser_id=b.id)AS c LEFT JOIN (SELECT id,realname,idcode,new_openid AS openid FROM tb_u_customer_user)AS d ON c.customer_user_id=d.id)AS e LEFT JOIN (SELECT id,name AS companyuser_banch,account AS companyuser_account FROM tb_u_level)AS f ON e.companyuser_level_id=f.id)AS g LEFT JOIN (SELECT id,name AS branch_name,account,city_name,district_name,account_nine FROM tb_u_level)AS h ON g.level_id=h.id)AS i LEFT JOIN (SELECT id,name AS activity_name FROM tb_cash_activity)AS j ON i.cash_activity_id=j.id)AS k LEFT JOIN (SELECT id,mchid,name AS mchid_name FROM tb_channel)AS l ON k.channel_id=l.id;
                '''
                cursor.execute(sql)
            ziduan = ['ID', '活动名称', '活动id', '所属市公司', '所属区县', '所属网点', '领取金额', '领取状态', '派发时间', '领取时间', '撤销时间', '用户openid', '领取人姓名', '领取人手机号',
                      '领取人身份证', '派发员工', '派发员工手机号', '派发员工所属网点', '派发员工网点机构号', '领取渠道', '派发服务商号', '派发流水号', '失败原因', '机构号']
            cols = ['id', 'activity_name', 'cash_activity_id', 'city_name', 'district_name', 'branch_name', 'amount', 'state', 'create_time', 'received_time', 'expired_time', 'openid', 'realname', 'mobile', 'idcode',
                    'companyuser_name', 'companyuser_mobile', 'companyuser_banch', 'companyuser_account', 'mchid_name', 'mchid', 'payment_no', 'return_msg', 'account_nine']
            response = StreamingHttpResponse(self.get_cashactivitydata_fromat(cols, cursor, conn, ziduan))
            response['Content-Type'] = 'application/octet-stream; charset=gbk'
            response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('微邮惠礼包派发数据.csv'))
            return response

    def get_cashactivitydata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        STATE_ENUM = {
            1: '待领取',
            2: '已领取',
            3: '领取失败',
            4: '已撤销'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'state':
                    tmp_str += '"%s",' % (STATE_ENUM[row[col]])
                elif col == 'is_white_table':
                    if row['is_white_table'] == 1:
                        tmp_str += '"%s",' % ('是')
                    else:
                        tmp_str += '"%s",' % ('否')
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                elif col == 'expired_time':
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row['state'] == 4 else '')
                # elif col == 'mchid_channel':
                #     if row['state'] in [2, 3]:
                #         if row['mchid'] == 'alipay':
                #             tmp_str += '"%s",' % '支付宝'
                #         else:
                #             tmp_str += '"%s",' % '微信'
                #     else:
                #         tmp_str += '"%s",' % ''
                elif col == 'mchid':
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"

    def destroy(self, request, *args, **kwargs):
        instance = self.get_object()
        user = self.request.iser
        if instance.state != 1:
            return Response({'error': '当前派发记录不可撤销'}, status=status.HTTP_400_BAD_REQUEST)
        # if instance.companyuser_id != user.id:
        #     return Response({'error': '您无权限撤销该派发'}, status=status.HTTP_400_BAD_REQUEST)
        # activity_level = instance.cash_activity_level
        cash_activity_id = instance.cash_activity_id
        cash_activity_level_id = instance.cash_activity_level_id
        activity = instance.cash_activity
        amount = instance.amount
        if activity.activity_type == 8:
            return Response({'error': '当前派发记录不可撤销'}, status=status.HTTP_400_BAD_REQUEST)
        if activity.activity_type in [1, 2, 3]:
            while True:
                activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
                origin_take_amount = activity_level.take_amount
                origin_remain_amount = activity_level.remain_amount
                origin_p_take_amount = activity_level.p_take_amount
                origin_p_remain_amount = activity_level.p_remain_amount
                new_take_amount = origin_take_amount - amount
                new_remain_amount = origin_remain_amount + amount
                new_p_take_amount = origin_p_take_amount - amount
                new_p_remain_amount = origin_p_remain_amount + amount
                result = CashActivityLevel.objects.filter(id=cash_activity_level_id, take_amount=origin_take_amount,
                                                          remain_amount=origin_remain_amount,
                                                          p_take_amount=origin_p_take_amount,
                                                          p_remain_amount=origin_p_remain_amount).update(
                    take_amount=new_take_amount, remain_amount=new_remain_amount, p_take_amount=new_p_take_amount,
                    p_remain_amount=new_p_remain_amount
                )
                if result == 0:
                    continue
                break
            while True:
                activity = CashActivity.objects.get(id=cash_activity_id)
                activity_origin_take_amount = activity.take_amount
                activity_origin_remain_amount = activity.remain_amount
                activity_new_take_amount = activity_origin_take_amount - amount
                activity_new_remain_amount = activity_origin_remain_amount + amount
                result = CashActivity.objects.filter(take_amount=activity_origin_take_amount,
                                                     remain_amount=activity_origin_remain_amount,
                                                     id=cash_activity_id).update(
                    take_amount=activity_new_take_amount, remain_amount=activity_new_remain_amount)
                if result == 0:
                    continue
                break
            relation_set = activity_level.child_set.all()
            for relation in relation_set:
                parent = relation.parent
                parent.p_take_amount -= amount
                parent.p_remain_amount += amount
                parent.save()
            date_now = datetime.datetime.now()
            instance.state = 4
            instance.expired_time = date_now
            instance.save()
            try:
                cashactivitylevelrecord_data = {}
                cashactivitylevelrecord_data['activity_level_id'] = activity_level.id
                cashactivitylevelrecord_data['amount'] = amount
                cashactivitylevelrecord_data['operation_type'] = 6
                cashactivitylevelrecord_data['remain_amount'] = activity_level.remain_amount
                cashactivitylevelrecord_data['p_remain_amount'] = activity_level.p_remain_amount
                cashactivitylevelrecord_data['max_amount'] = activity_level.max_amount
                cashactivitylevelrecord_data['p_max_amount'] = activity_level.p_max_amount
                cashactivitylevelrecord_data['user_id'] = user.id
                cashactivitylevelrecord_data['remark'] = '管理员撤销派发二维码'
                cashactivitylevelrecord_data['activity_data_id'] = instance.id
                cashactivitylevelrecord_data['activity_id'] = activity.id
                CashActivityLevelRecord.objects.create(**cashactivitylevelrecord_data)
                if instance.task_id:
                    celery_app.control.revoke(instance.task_id)
            except Exception as e:
                print(e)
                pass
        else:
            while True:
                activity_level = CashActivityLevel.objects.get(id=cash_activity_level_id)
                origin_take_amount = activity_level.take_amount
                origin_max_amount = activity_level.max_amount
                origin_p_take_amount = activity_level.p_take_amount
                origin_p_max_amount = activity_level.p_max_amount
                new_take_amount = origin_take_amount - amount
                new_max_amount = origin_max_amount - amount
                new_p_take_amount = origin_p_take_amount - amount
                new_p_max_amount = origin_p_max_amount - amount
                result = CashActivityLevel.objects.filter(id=cash_activity_level_id, take_amount=origin_take_amount,
                                                          max_amount=origin_max_amount,
                                                          p_take_amount=origin_p_take_amount,
                                                          p_max_amount=origin_p_max_amount).update(
                    take_amount=new_take_amount, max_amount=new_max_amount, p_take_amount=new_p_take_amount,
                    p_max_amount=new_p_max_amount
                )
                if result == 0:
                    continue
                break
            while True:
                activity = CashActivity.objects.get(id=cash_activity_id)
                activity_origin_take_amount = activity.take_amount
                activity_origin_remain_amount = activity.remain_amount
                activity_new_take_amount = activity_origin_take_amount - amount
                activity_new_remain_amount = activity_origin_remain_amount + amount
                result = CashActivity.objects.filter(take_amount=activity_origin_take_amount,
                                                     remain_amount=activity_origin_remain_amount,
                                                     id=cash_activity_id).update(
                    take_amount=activity_new_take_amount, remain_amount=activity_new_remain_amount)
                if result == 0:
                    continue
                break
            relation_set = activity_level.child_set.all()
            for relation in relation_set:
                parent = relation.parent
                parent.p_take_amount -= amount
                parent.p_max_amount -= amount
                parent.save()
            date_now = datetime.datetime.now()
            instance.state = 4
            instance.expired_time = date_now
            instance.save()
            try:
                cashactivitylevelrecord_data = {}
                cashactivitylevelrecord_data['activity_level_id'] = activity_level.id
                cashactivitylevelrecord_data['amount'] = amount
                cashactivitylevelrecord_data['operation_type'] = 6
                cashactivitylevelrecord_data['activity_type'] = 2
                cashactivitylevelrecord_data['remain_amount'] = activity.remain_amount
                cashactivitylevelrecord_data['p_remain_amount'] = activity.remain_amount
                cashactivitylevelrecord_data['max_amount'] = activity.max_amount
                cashactivitylevelrecord_data['p_max_amount'] = activity_level.p_max_amount
                cashactivitylevelrecord_data['user_id'] = user.id
                cashactivitylevelrecord_data['remark'] = '管理员撤销派发二维码'
                cashactivitylevelrecord_data['activity_data_id'] = instance.id
                cashactivitylevelrecord_data['activity_id'] = activity.id
                CashActivityLevelRecord.objects.create(**cashactivitylevelrecord_data)
                if instance.task_id:
                    celery_app.control.revoke(instance.task_id)
            except Exception as e:
                print(e)
                pass
        try:
            if activity.activity_type in [2, 5]:
                cash_activity_rule = instance.cash_activity_rule
                cash_activity_rule.probability += 1
                cash_activity_rule.save()
        except:
            pass
        return Response({'success': '撤销成功'}, status=status.HTTP_200_OK)


class CashActivityDataKeguanModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = CashActivityDataKeguanFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('cashactivitydata__payment_no', )

    def get_queryset(self):
        queryset = CashActivityDataKeguan.objects.all()
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return CashActivityDataKeguanListModelSerializer


class CashActivityLevelRecordModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = CashActivityLevelRecordFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('user__nickname', )
    serializer_class = CashActivityLevelRecordModelSerializer

    def get_queryset(self):
        return CashActivityLevelRecord.objects.all().order_by('-create_time')


class MerchantBankQRCodeQuestModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeQuestFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    # search_fields = ('user__nickname', )
    serializer_class = MerchantBankQRCodeQuestModelSerializer
    queryset = MerchantBankQRCodeQuest.objects.all().order_by('-id')

    def create(self, request, *args, **kwargs):
        my_file = self.request.FILES.get('my_file', None)
        file_type = self.request.data.get('file_type', None)
        user = self.request.iser
        if not my_file:
            return Response({'error': '请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        if str(file_type) not in ['1', '2', '3']:
            return Response({'error': '上传类型有误'}, status=status.HTTP_400_BAD_REQUEST)
        type_excel = my_file.name.split('.')[-1]
        file_contents = my_file.read()
        if type_excel not in ['xlsx', 'xls']:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        try:
            wb = xlrd.open_workbook(filename=None, file_contents=file_contents)  # 关键点在于这里
        except:
            return Response({'error': '文件格式有误, 请上传excel文件'}, status=status.HTTP_400_BAD_REQUEST)
        table = wb.sheets()[0]
        nrows = table.nrows
        title = table.row_values(0)
        if str(file_type) == '1':
            if '员工编码' not in title:
                return Response({'error': '表格字段缺少"员工编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '员工码下载地址' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
        elif str(file_type) == '3':
            if '日期' not in title:
                return Response({'error': '表格字段缺少"日期"'}, status=status.HTTP_400_BAD_REQUEST)
            if '当日新增银行首绑量' not in title:
                return Response({'error': '表格字段缺少"当日新增银行首绑量"'}, status=status.HTTP_400_BAD_REQUEST)
            if '当日引导绑卡数' not in title:
                return Response({'error': '表格字段缺少"当日引导绑卡数"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
        else:
            if '绑卡时间' not in title:
                return Response({'error': '表格字段缺少"绑卡时间"'}, status=status.HTTP_400_BAD_REQUEST)
            if '绑卡银行卡号' not in title:
                return Response({'error': '表格字段缺少"绑卡银行卡号"'}, status=status.HTTP_400_BAD_REQUEST)
            if '是否银行首绑' not in title:
                return Response({'error': '表格字段缺少"是否银行首绑"'}, status=status.HTTP_400_BAD_REQUEST)
            if '子码编码' not in title:
                return Response({'error': '表格字段缺少"子码编码"'}, status=status.HTTP_400_BAD_REQUEST)
            if '绑卡类型' not in title:
                return Response({'error': '表格字段缺少"绑卡类型"'}, status=status.HTTP_400_BAD_REQUEST)
        file_name = f'{my_file.name.replace(type_excel, "").replace(".", "")}_{datetime.datetime.now().strftime("%Y%m%d%H%M%S")}.{type_excel}'
        file = pic_upload(file_contents, file_name)
        check_quest = MerchantBankQRCodeQuest.objects.filter(file_status=2)
        if check_quest:
            file_status = 1
        else:
            file_status = 2
        quest = MerchantBankQRCodeQuest.objects.create(create_user_id=user.id, file=file, file_type=file_type, file_status=file_status, total_line=nrows-1)
        if file_status == 2:
            operation_merchant_bank_quest.delay({'id': quest.id})
            # operation_merchant_bank_quest({'id': quest.id})
        return Response({'success': '上传文件成功'}, status=status.HTTP_200_OK)


class MerchantBankQRCodeModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        sort = self.request.query_params.get('sort', '2')
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCode.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCode.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCode.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCode.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBankQRCode.objects.filter(level__branch_id=level.id)
        if str(sort) == '1':
            queryset = queryset.order_by('visit', 'id')
        if str(sort) == '2':
            queryset = queryset.order_by('-visit', 'id')
        return queryset

    def get_serializer_class(self):
        if self.action == 'list':
            return MerchantBankQRCodeListModelSerializer
        else:
            return MerchantBankQRCodeRetrieveModelSerializer

    def export_bankqrcode(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        search = self.request.query_params.get('search', None)
        is_download = self.request.query_params.get('is_download', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if is_download:
            condition = condition + f' AND is_download in ({is_download})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        # try:
        #     sql = f'''
        #     SELECT i.*,j.yesterday_first_number,yesterday_number FROM (SELECT g.*,h.last_month_number,last_month_first_number FROM (SELECT e.*,f.this_month_number,this_month_first_number FROM (SELECT c.*,d.total_first_number,total_number FROM (SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,level_id,is_download,visit FROM tb_merchant_bank_qrcode {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT bank_qrcode_id,COUNT(id)AS total_number,SUM(is_first)AS total_first_number FROM tb_merchant_bank_qrcode_bind_data GROUP BY bank_qrcode_id)AS d ON c.id=d.bank_qrcode_id)AS e LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS this_month_number,SUM(is_first)AS this_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE DATE(bind_time) >= CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND DATE(bind_time) < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) GROUP BY bank_qrcode_id)AS f ON e.id=f.bank_qrcode_id)AS g LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS last_month_number,SUM(is_first)AS last_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE YEAR(bind_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND MONTH(bind_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND DAY(bind_time) >= 1 GROUP BY bank_qrcode_id)AS h ON g.id=h.bank_qrcode_id)AS i LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS yesterday_number,SUM(is_first)AS yesterday_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE bind_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND bind_time < CURDATE() GROUP BY bank_qrcode_id)AS j ON i.id=j.bank_qrcode_id;
        #     '''
        #     cursor.execute(sql)
        # except:
        #     sql = f'''
        #     SELECT i.*,j.yesterday_first_number,yesterday_number FROM (SELECT g.*,h.last_month_number,last_month_first_number FROM (SELECT e.*,f.this_month_number,this_month_first_number FROM (SELECT c.*,d.total_first_number,total_number FROM (SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,level_id,is_download,visit FROM tb_merchant_bank_qrcode)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id)AS c LEFT JOIN (SELECT bank_qrcode_id,COUNT(id)AS total_number,SUM(is_first)AS total_first_number FROM tb_merchant_bank_qrcode_bind_data GROUP BY bank_qrcode_id)AS d ON c.id=d.bank_qrcode_id)AS e LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS this_month_number,SUM(is_first)AS this_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE DATE(bind_time) >= CURDATE() - INTERVAL DAY(CURDATE()) - 1 DAY AND DATE(bind_time) < DATE_ADD(CURDATE(), INTERVAL 1 MONTH) GROUP BY bank_qrcode_id)AS f ON e.id=f.bank_qrcode_id)AS g LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS last_month_number,SUM(is_first)AS last_month_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE YEAR(bind_time) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND MONTH(bind_time) = MONTH(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))AND DAY(bind_time) >= 1 GROUP BY bank_qrcode_id)AS h ON g.id=h.bank_qrcode_id)AS i LEFT JOIN (SELECT bank_qrcode_id,COUNT(id) AS yesterday_number,SUM(is_first)AS yesterday_first_number FROM tb_merchant_bank_qrcode_bind_data WHERE bind_time >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) AND bind_time < CURDATE() GROUP BY bank_qrcode_id)AS j ON i.id=j.bank_qrcode_id;
        #     '''
        #     cursor.execute(sql)
        try:
            sql = f'''SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT * FROM tb_merchant_bank_qrcode {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;'''
            cursor.execute(sql)
        except:
            sql = f'''SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT * FROM tb_merchant_bank_qrcode)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;'''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '是否下载', '浏览次数', '累计绑卡数', '累计首绑数']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'is_download', 'visit', 'total_number', 'total_first_number']
        response = StreamingHttpResponse(self.get_bankqrcode_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡统计.csv'))
        return response

    def get_bankqrcode_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_DOWNLOAD_ENUM = {
            0: '否',
            1: '是'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col in ['yesterday_number', 'yesterday_first_number', 'this_month_number', 'this_month_first_number', 'last_month_number', 'last_month_first_number', 'total_number', 'total_first_number']:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '0')
                elif col == 'is_download':
                    tmp_str += '"%s",' % (IS_DOWNLOAD_ENUM[row[col]])
                elif col == 'code_id':
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQRCodeBindDataModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MerchantBankQRCodeBindDataFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MerchantBankQRCodeBindData.objects.all()
        elif level.level == 1:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__district_id=level.id)
        else:
            queryset = MerchantBankQRCodeBindData.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MerchantBankQRCodeBindDataListModelSerializer

    def export_bankqrcodedata(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        card_type = self.request.query_params.get('card_type', None)
        is_first = self.request.query_params.get('is_first', None)
        bank_qrcode_id = self.request.query_params.get('bank_qrcode_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        if card_type:
            condition = condition + f' AND card_type in ({card_type})'
        if is_first:
            condition = condition + f' AND is_first in ({is_first})'
        if bank_qrcode_id:
            condition = condition + f' AND bank_qrcode_id in ({bank_qrcode_id})'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,is_first,card_type,card_number,level_id FROM tb_merchant_bank_qrcode_bind_data {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,is_first,card_type,card_number,level_id FROM tb_merchant_bank_qrcode_bind_data)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '绑卡类型', '绑卡时间', '绑卡银行卡号', '是否银行首绑']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'bind_time', 'card_type', 'bind_time', 'card_number', 'is_first']
        response = StreamingHttpResponse(self.get_bankqrcodedata_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡明细.csv'))
        return response

    def get_bankqrcodedata_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_FIRST_ENUM = {
            0: '否',
            1: '是'
        }
        CARD_TYPE_ENUM = {
            1: '储蓄卡',
            2: '信用卡'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_first':
                    tmp_str += '"%s",' % (IS_FIRST_ENUM[row[col]])
                elif col == 'card_type':
                    tmp_str += '"%s",' % (CARD_TYPE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"


class MerchantBankQPCodeStatisticsModelViewSet(ModelViewSet):

    pagination_class = MyPage
    filter_class = MErchantBankQPCodeStatisticsFilter
    filter_backends = (filters.SearchFilter, DjangoFilterBackend)
    search_fields = ('code_id', 'business_no', 'merchant_name')

    def get_queryset(self):
        user = self.request.iser
        level = user.level
        level_id = self.request.query_params.get('level_id', None)
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            queryset = MErchantBankQPCodeStatistics.objects.all()
        elif level.level == 1:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__prov_id=level.id)
        elif level.level == 2:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__city_id=level.id)
        elif level.level == 3:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__district_id=level.id)
        else:
            queryset = MErchantBankQPCodeStatistics.objects.filter(level__branch_id=level.id)
        return queryset.order_by('-id')

    def get_serializer_class(self):
        return MErchantBankQPCodeStatisticsListModelSerializer

    def export_bankqrcodestatistics(self, request):
        user = self.request.iser
        level_id = self.request.query_params.get('level_id', None)
        bank_qrcode_id = self.request.query_params.get('bank_qrcode_id', None)
        bind_time_begin = self.request.query_params.get('bind_time_begin', None)
        bind_time_end = self.request.query_params.get('bind_time_end', None)
        search = self.request.query_params.get('search', None)
        level = user.level
        if level_id:
            try:
                level_list = get_level_list(level)
                level_id = int(level_id)
                if level_id in level_list:
                    level = Level.objects.filter(id=level_id).first()
            except:
                pass
        if level.level == 0:
            condition = 'WHERE id != 0'
        elif level.level == 1:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE prov_id={level.id})'
        elif level.level == 2:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE city_id={level.id})'
        elif level.level == 3:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE district_id={level.id})'
        else:
            condition = f'WHERE level_id IN (SELECT id FROM tb_u_level WHERE branch_id={level.id})'
        if search:
            condition = condition + f' AND (code_id LIKE "%{search}%" OR business_no LIKE "%{search}%" OR merchant_name LIKE "%{search}%")'
        if bank_qrcode_id:
            condition = condition + f' AND bank_qrcode_id in ({bank_qrcode_id})'
        if bind_time_begin:
            condition = condition + f' AND bind_time >= "{bind_time_begin + " 00:00:00"}"'
        if bind_time_end:
            condition = condition + f' AND bind_time <= "{bind_time_end + " 23:59:59"}"'
        conn = pymysql.connect(host=settings.DATABASES['default']['HOST'],
                               port=settings.DATABASES['default']['PORT'],
                               database=settings.DATABASES['default']['NAME'],
                               user=settings.DATABASES['default']['USER'],
                               password=settings.DATABASES['default']['PASSWORD'])
        cursor = conn.cursor(cursor=pymysql.cursors.SSDictCursor)
        try:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,level_id,total_number,total_first_number FROM tb_merchant_bank_qrcode_statistics {condition})AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        except:
            sql = f'''
            SELECT a.*,b.city_name,district_name,branch_name,account_nine FROM (SELECT id,code_id,business_no,merchant_name,DATE_FORMAT(bind_time,'%Y-%m-%d %H:%i:%S')AS bind_time,level_id,total_number,total_first_number FROM tb_merchant_bank_qrcode_statistics)AS a LEFT JOIN (SELECT id,city_name,district_name,branch_name,account_nine FROM tb_u_level)AS b ON a.level_id=b.id;
            '''
            cursor.execute(sql)
        ziduan = ['ID', '子码编码', '微邮付商户号', '商户名称', '所属市公司', '所属区县', '所属网点', '机构号', '绑卡时间', '当日引导绑卡数', '当日新增银行首绑量']
        cols = ['id', 'code_id', 'business_no', 'merchant_name', 'city_name', 'district_name', 'branch_name', 'account_nine', 'bind_time', 'total_number', 'total_first_number']
        response = StreamingHttpResponse(self.get_bankqrcodestatistics_fromat(cols, cursor, conn, ziduan))
        response['Content-Type'] = 'application/octet-stream; charset=gbk'
        response['Content-Disposition'] = "attachment; filename*=utf-8''{}".format(escape_uri_path('商户银行卡绑卡统计.csv'))
        return response

    def get_bankqrcodestatistics_fromat(self, cols, cursor, conn, ziduan):
        tmp_str = ""
        # 返回文件的每一列列名
        for col in ziduan:
            tmp_str += '"%s",' % (col)
        yield tmp_str.strip(",") + "\n"
        IS_FIRST_ENUM = {
            0: '否',
            1: '是'
        }
        CARD_TYPE_ENUM = {
            1: '储蓄卡',
            2: '信用卡'
        }
        while True:
            tmp_str = ""
            row = cursor.fetchone()
            if row is None:
                cursor.close()
                conn.close()
                break
            for col in cols:
                if col == 'is_first':
                    tmp_str += '"%s",' % (IS_FIRST_ENUM[row[col]])
                elif col == 'card_type':
                    tmp_str += '"%s",' % (CARD_TYPE_ENUM[row[col]])
                elif col in ['idcode', 'payment_no']:
                    tmp_str += '"\'%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
                else:
                    tmp_str += '"%s",' % (str(row[col]).encode('GBK', 'ignore').decode('gbk') if row[col] else '')
            yield tmp_str.strip(',') + "\n"